Multi-select Fields from Airtable Display as [List] in Power BI - Seeking Assistance to Retrieve Actual Values

Other Questions Airtable
1

I’m experiencing difficulties when trying to import data from Airtable into Power BI. Specifically, fields that allow multiple selections (like dropdowns with several options) appear only as “[List]” rather than showing the actual selections.

Issue Detail:
In my Airtable setup, there are fields where users select from dropdown lists. For instance, a field labeled “Region” might include choices such as “Asia”, “Europe”, and “Americas”. However, when I bring this data into Power BI, these fields show up simply as “[List]”, and I can’t view the specific selections.

Steps Attempted:
I’ve utilized PowerQuery with custom M code to facilitate the connection to Airtable. The connection seems to work well for standard text and numerical fields, but the multi-select fields are causing me problems. Additionally, I tried creating alternative columns in Airtable with different field types, but without success.

Code I’m Using:

let 
    // API pagination management for fetching all records
    GetPages = List.Skip(
        List.Generate(
            () => [NextPage = "start", Count=0],
            each [NextPage] <> null,
            each [
                NextPage = try if [Count] < 1 then "" else [APIResponse][Value][offset] otherwise null,
                APIResponse = try if [Count] < 1 then
                    Json.Document(
                        Web.Contents(
                            "https://api.airtable.com",
                            [
                                RelativePath = "v0/" & DATABASE_ID & "/" & SHEET_ID & "?view=" & VIEW_NAME,
                                Headers = [Authorization = "Bearer " & ACCESS_TOKEN]
                            ]
                        )
                    )
                else
                    Json.Document(
                        Web.Contents(
                            "https://api.airtable.com",
                            [
                                RelativePath = "v0/" & DATABASE_ID & "/" & SHEET_ID & "?view=" & VIEW_NAME & "&offset=" & [APIResponse][Value][offset],
                                Headers = [Authorization = "Bearer " & ACCESS_TOKEN]
                            ]
                        )
                    ),
                Count = [Count] + 1
            ],
            each [APIResponse]
        ),
        1
    ),

    // Convert API response into table format
    #"Made Table" = Table.FromList(
        GetPages, Splitter.SplitByNothing(), null, null, ExtraValues.Error
    ),

    // Expand and extract nested data structures
    #"Expanded Data" = Table.ExpandRecordColumn(
        #"Made Table", "Column1", {"Value"}, {"Column1.Value"}
    ),
    #"Expanded Values" = Table.ExpandRecordColumn(
        #"Expanded Data", "Column1.Value", {"records"}, {"Column1.Value.records"}
    ),
    #"Expanded Records" = Table.ExpandListColumn(
        #"Expanded Values", "Column1.Value.records"
    ),
    #"Expanded Details" = Table.ExpandRecordColumn(
        #"Expanded Records", "Column1.Value.records",
        {"id", "fields", "createdTime"},
        {"Column1.Value.records.id", "Column1.Value.records.fields", "Column1.Value.records.createdTime"}
    ),

    // Standardize column naming
    #"Fixed Names" = Table.RenameColumns(
        #"Expanded Details",
        {
            {"Column1.Value.records.id", "_recordID"},
            {"Column1.Value.records.createdTime", "_createdDate"},
            {"Column1.Value.records.fields", "_recordData"}
        }
    ),

    // Organize columns in a specific order
    #"Fixed Order" = Table.ReorderColumns(
        #"Fixed Names",
        {"_recordID", "_createdDate", "_recordData"}
    ),

    // Dynamically expand all field data
    #"Expanded Fields" = Table.ExpandRecordColumn(
        #"Fixed Order", "_recordData",
        List.Distinct(List.Combine(List.Transform(
            List.Transform(Table.ToRecords(#"Fixed Order"), each Record.Field(_, "_recordData")),
            each Record.FieldNames(_)
        ))),
        List.Distinct(List.Combine(List.Transform(
            List.Transform(Table.ToRecords(#"Fixed Order"), each Record.Field(_, "_recordData")),
            each Record.FieldNames(_)
        )))
    )
in
    #"Expanded Fields"

I’m not very knowledgeable about Power BI or M language, so detailed, step-by-step instructions would be invaluable. Is there a method to expand these list values so I can see the actual options chosen instead of just seeing “[List]”? Any assistance would be greatly appreciated as I’ve been at a standstill with this for several days.

2

This happens because Airtable’s multi-select fields return arrays, and Power BI sees them as list objects. After your final transformation, you’ll need to convert these list columns. I’ve found Table.TransformColumns() with Text.Combine() works better than expanding directly. Add this after your last step: Table.TransformColumns(#"Expanded Fields", {{"Region", each if _ is list then Text.Combine(_, ", ") else _}}). Just swap “Region” for your actual column name. This turns the list into comma-separated text showing all selected values. If you want separate rows for each selection instead, use Luna’s expand method - but heads up, it’ll duplicate your records.

3

Been there with this exact headache. Airtable sends multi-select fields as JSON arrays, and your code isn’t handling them properly.

Here’s what I do differently. Instead of expanding everything at once, I handle list columns separately after the main expansion. Add this after your #“Expanded Fields” step:

#"Convert Lists" = Table.TransformColumns(
    #"Expanded Fields",
    List.Transform(
        Table.ColumnNames(#"Expanded Fields"),
        each {_, (value) => if Value.Is(value, type list) then Text.Combine(List.Transform(value, Text.From), "; ") else value}
    )
)

This finds any column with lists and converts them to semicolon-separated text. No manual column naming needed.

I’ve used this on several client projects pulling survey data from Airtable. Works every time and handles edge cases like empty selections or mixed data types.

If you want each selection as a separate row instead of combined text, that’s doable but creates data duplication issues downstream.

4

yeah, i faced that too! just try adding an expand step after the last one: Table.ExpandListColumn(#"Expanded Fields", "your_multiselect_column_name"). that should convert those [List] values into the actual options. you might have to do this for each multiselect field separately tho.