Importing Multi-Select Fields from Airtable to Power BI: Data Appears as 'List'

Other Questions Airtable
1

Hey folks, I’m stuck with a problem importing data from Airtable to Power BI. The multi-select columns in Airtable are showing up as ‘List’ in Power BI instead of the actual values. For example, a column with country options like Indonesia, Philippines, Qatar, etc., just shows as ‘[List]’ in Power BI.

I’ve used Power Query to retrieve the data from an Airtable view. While all other columns work as expected, these multi-select ones are causing issues.

Below is a revised version of the code I used:

let
    Source = Json.Document(Web.Contents("https://api.airtable.com/v0/myBaseId/myTableId?view=myViewId", [Headers=[Authorization="Bearer myToken"]])),
    records = Source[records],
    ConvertedTable = Table.FromList(records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandedRecords = Table.ExpandRecordColumn(ConvertedTable, "Column1", {"fields"}, {"fields"}),
    ExpandedFields = Table.ExpandRecordColumn(ExpandedRecords, "fields", {"Country", "OtherField"}, {"Country", "OtherField"})
in
    ExpandedFields

I’ve tried switching columns and searched extensively online, but nothing has fixed this issue. Does anyone have a suggestion for retrieving the full values instead of just ‘List’? Any help would be appreciated!

2

Having dealt with similar Airtable-to-Power BI issues, I can suggest an alternative approach. Instead of using the API directly, consider exporting your Airtable view as a CSV file first. Then, import this CSV into Power BI. This method often preserves multi-select fields more accurately, avoiding the ‘List’ problem.

If you must use the API, try modifying your M code to explicitly expand the multi-select columns. After your ExpandedFields step, add:

ExpandedMultiSelect = Table.ExpandListColumn(ExpandedFields, "Country")

This should force Power BI to show individual values. Be aware this might create multiple rows per record if there are multiple selections. Adjust your data model accordingly to handle this potential one-to-many relationship.

3

hey, i’ve had this problem too. what worked for me was using the List.Transform function in Power Query. try adding this step:

TransformedCountry = Table.TransformColumns(ExpandedFields, {“Country”, each Text.Combine(List.Transform(_, Text.From), ", ")})

this should combine the list items into a single string. hope it helps!

4

I’ve encountered this issue before when working with Airtable and Power BI. The problem stems from how Airtable stores multi-select fields as arrays, which Power BI interprets as lists.

To resolve this, you need to expand the list column after importing. In Power Query Editor, click on the expand column button (two opposing arrows) next to the multi-select field. Choose ‘Expand to New Rows’ option. This will create separate rows for each selected value.

If you prefer keeping all values in one cell, use the ‘Extract Values’ function instead. Add a custom column with this formula:

= Text.Combine(YourListColumn, ", ")

This joins all values into a single text string, separated by commas.

Remember to adjust data types after expansion or extraction. These steps should give you the actual values instead of [List].