Importing Airtable data via PowerQuery causes multi-select fields to show up as generic lists in PowerBI. The custom query returns summary lists instead of individual values. How can I retrieve detailed entries?
let
entries = List.Generate(
() => [nextKey = "start", index = 0],
each [nextKey] <> null,
each let
result = if [index] = 0 then
Json.Document(Web.Contents("https://api.airtable.com/v0/" & baseKey & "/" & tableName & "?view=" & viewLabel, [Headers=[Authorization="Bearer " & secretToken]]))
else
Json.Document(Web.Contents("https://api.airtable.com/v0/" & baseKey & "/" & tableName & "?view=" & viewLabel & "&offset=" & [nextKey], [Headers=[Authorization="Bearer " & secretToken]])),
updatedKey = try result[offset] otherwise null
in
[nextKey = updatedKey, index = [index] + 1, output = result],
each [output]
),
resultTable = Table.FromList(entries, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
resultTable
hey, i fixed a similar issue by using Table.ExpandListColumn right after pulling the data. it seperated each multi-select value into its own row so you get the details. maybe worth a try if u havent already.
An alternative solution that worked for me was to apply a custom transformation that specifically targets the multi-select fields. I found that using Table.TransformColumns with List.Transform allowed me to convert the list entries into a more descriptive text format. In my case, this method gave me more flexibility as I could define exactly how each list element should be handled rather than relying solely on Table.ExpandListColumn. I implemented a step to concatenate details and create separate columns, which helped maintain the granularity needed for in-depth analysis in PowerBI.
In my experience, the key is to add an extra step to expand the list fields after retrieving your data. I encountered a similar issue when working with Airtable data. I ended up modifying my query to use the Table.ExpandListColumn function after converting the data with Table.FromList. Essentially, once you have your multi-select field as a list, you need to apply a transformation that splits each element into separate rows. This method provides a more detailed view of your multi-select data, making it easier to analyze in PowerBI.
I found that a more nuanced approach worked well for me. I started by modifying my query to incorporate a custom function that serialized the multi-select list into a record, which was then expanded into individual columns before any aggregation took place. This step involved using a combination of Record.ToTable and a specific transformation to ensure that each element in the multi-select list was treated as a distinct field. This approach not only preserved the original data fidelity, but also allowed me to perform targeted clean-up operations, which ultimately enhanced my analysis in PowerBI.