Airtable to Power BI - Link to another record issue

If [Table 1] contains a link to [Table 2], if you import the linked record field itself, you’ll get the RECORD_ID()(s) of the linked record(s) — that’s what reciA4xEtvtX4AonV is. To import the actual field values from [Table 2], you’ll have to define lookup or rollup fields in [Table 1] to reference whatever data you wish to pull.

What I often do — and it’s been a while since I dealt with Power BI, so I’m not sure how much of a hassle it might be to ‘unpack’ the value on the receiving end — is create a ‘portmanteau’ value in my linked record and reference that via lookup. For instance, if I need multiple values from [Table 2], I would create a field in that table that concatenates those values together in a fixed-length or character-delimited string, depending on the type of data to be moved:

001|0034.3|0000|0035
or
FirstName|LastName|Address1|City|ST|Zip+4

I’d call that field something like {Table 2::ValueStr} — that is, the {ValueStr} field in [Table 2]. In [Table 1], I’d define a lookup field that followed the link to [Table 2] and referenced {ValueStr}. Finally, I’d import that field into Power BI and use its internal parsing process to break out the individual values.

If the linked record field permitted linking to multiple records, I’d use a rollup field with an aggregation formula of '^'&ARRAYJOIN(values,'^'). That would let me search for ‘^’ to find the start of each portmanteau record and ‘|’ to extract out individual values.

On re-reading, I realize that is probably the least-coherent description of the process possible. If you have questions, please ask, and I’ll try again after the meds wear off. (Dental nightmare.) :winking_face:

It depends on what values you are trying to get from the “link to another record”. I had this same issue with my product managers converted into the record id into PowerBi. This is an easy fix since I’m not adding more product managers to the table.

I simply went back to Airtable and looked up the product ID to the product manager and had PowerBi change the record id to the product manager name. Hope this will work for you as well.

Thanks everyone for your suggestions, I’m sure they all work great. After spending some more time trying to figure this out, I realized Power BI was not only importing the Owner’s ID but also the Owner’s Name column. So I just had to create a new column in Power BI and use the following formula to make that lookup trick on the imported tables.

Task Owner = LOOKUPVALUE(‘Owners_Table’[Value.fields.Name],‘Owners_Table’[Value.id],‘Tasks_Table’[Value.fields.Owner])

Task Owner: Name of the new calculated table in Power BI
Owners_Table: In Airtable, this is the table where all task owners list is
Tasks_Table: In Airtable, this is the table that has a look up column to the Owners_Table, to determine the task owner

thanks all!!