Airtable Multi-select Columns Disappear as [List] in Power BI Instead of Individual Entries

Other Questions Airtable
1

I’m facing a challenge when transferring data from Airtable into Power BI. Specifically, columns that contain multiple selections are only showing up as [List] instead of the individual selections.

For instance, there’s a dropdown in Airtable for selecting various countries such as Vietnam, India, and Japan. However, upon importing this data into Power BI, it shows [List] rather than the actual country names that were selected.

I’ve been utilizing Power Query along with some custom M code to connect to my Airtable view. Below is the snippet of the code I have been working with:

let 
    // Handle pagination to fetch data from Airtable in batches
    PagesOfData = List.Skip(
        List.Generate(
            () => [NextPageToken = "firstPage", Counter=0],
            each [NextPageToken] <> null,
            each [
                NextPageToken = try if [Counter] < 1 then "" else [Response][Value][offset] otherwise null,
                Response = try if [Counter] < 1 then
                    Json.Document(
                        Web.Contents(
                            "https://api.airtable.com",
                            [
                                RelativePath = "v0/" & DB_ID & "/" & SHEET_NAME & "?view=" & VIEW_SET,
                                Headers = [Authorization = "Bearer " & TOKEN]
                            ]
                        )
                    )
                else
                    Json.Document(
                        Web.Contents(
                            "https://api.airtable.com",
                            [
                                RelativePath = "v0/" & DB_ID & "/" & SHEET_NAME & "?view=" & VIEW_SET & "&offset=" & [Response][Value][offset],
                                Headers = [Authorization = "Bearer " & TOKEN]
                            ]
                        )
                    ),
                Counter = [Counter] + 1
            ],
            each [Response]
        ),
        1
    ),

    // Format the paginated response into a table structure
    #"Created Table" = Table.FromList(
        PagesOfData, Splitter.SplitByNothing(), null, null, ExtraValues.Error
    ),

    // Extract and expand the nested data fields
    #"Unpacked Column1" = Table.ExpandRecordColumn(
        #"Created Table", "Column1", {"Value"}, {"Column1.Value"}
    ),
    #"Unpacked Column1.Value" = Table.ExpandRecordColumn(
        #"Unpacked Column1", "Column1.Value", {"records"}, {"Column1.Value.records"}
    ),
    #"Unpacked Column1.Value.records" = Table.ExpandListColumn(
        #"Unpacked Column1.Value", "Column1.Value.records"
    ),
    #"Unpacked Column1.Value.recordsDetails" = Table.ExpandRecordColumn(
        #"Unpacked Column1.Value.records", "Column1.Value.records",
        {"id", "fields", "createdTime"},
        {"Column1.Value.records.id", "Column1.Value.records.fields", "Column1.Value.records.createdTime"}
    ),

    // Rename columns for better clarity
    #"Renamed Columns" = Table.RenameColumns(
        #"Unpacked Column1.Value.recordsDetails",
        {
            {"Column1.Value.records.id", "_recordID"},
            {"Column1.Value.records.createdTime", "_recordTimestamp"},
            {"Column1.Value.records.fields", "_recordFields"}
        }
    ),

    // Rearrange columns to match expected layout
    #"Rearranged Columns" = Table.ReorderColumns(
        #"Renamed Columns",
        {"_recordID", "_recordTimestamp", "_recordFields"}
    ),

    // Expand field data dynamically to adapt to potential schema modifications
    #"Expanded Field Data" = Table.ExpandRecordColumn(
        #"Rearranged Columns", "_recordFields",
        List.Distinct(List.Combine(List.Transform(
            List.Transform(Table.ToRecords(#"Rearranged Columns"), each Record.Field(_, "_recordFields")),
            each Record.FieldNames(_)
        ))),
        List.Distinct(List.Combine(List.Transform(
            List.Transform(Table.ToRecords(#"Rearranged Columns"), each Record.Field(_, "_recordFields")),
            each Record.FieldNames(_)
        )))
    )
in
    #"Expanded Field Data"

I lack extensive experience with Power Query and M language, so I would appreciate any guidance on how to effectively unpack these list values. I’ve attempted creating new columns in Airtable and researched solutions, but nothing has worked so far. Any help on how to adjust this code for correctly displaying the multi-select values would be greatly valued!

2

Had this exact problem six months ago with Airtable survey data. Multi-select columns always show as [List] until you handle them in Power Query. I add a step right after the final expansion to catch and transform list columns automatically. Skip the manual column naming - use this instead: #"Transform List Columns" = Table.TransformColumns( #"Expanded Field Data", List.Transform( Table.ColumnsOfType(#"Expanded Field Data", {type list}), each {_{0}, (x) => if x = null then null else Text.Combine(List.Transform(x, Text.From), "; ")} ) ) This finds any leftover list columns and converts them to semicolon-separated text. Text.From handles non-string list items. I use semicolons because Airtable values often have commas already. Watch out for lookup fields that reference multi-select columns - they create nested lists needing extra work. But this handles standard multi-select fields fine.

3

Had this exact problem connecting Airtable CRM data to Power BI last year. Multi-select fields come through as lists that you’ve got to expand properly. Your API connection code looks good, but you need one more step for those list columns. After your final expand, identify the list columns and run Table.ExpandListColumn on each one. Here’s the thing - if you want each multi-select value on separate rows, expand them one by one. Want them in a single cell instead? Use Text.Combine with Table.TransformColumns. I’ve found concatenation works way better for reporting. I usually add a Table.TransformColumns step that converts each list column to text using Text.Combine with a semicolon or comma delimiter. Keeps all the selected values readable without creating duplicate rows for every selection.

4

The [List] issue hits everyone working with Airtable multi-selects. I’ve dealt with this countless times.

Your API code looks good, but Power Query needs explicit instructions for handling list values.

After your “Expanded Field Data” step, check which columns are still lists:

#"Check Column Types" = Table.ColumnsOfType(#"Expanded Field Data", {type list})

For each multi-select column, you’ve got two options. I recommend putting all values in one cell for most reports:

#"Convert Lists to Text" = Table.TransformColumns(
    #"Expanded Field Data",
    {{"YourCountryColumn", each if _ = null then null else Text.Combine(_, ", ")}, 
     {"AnotherMultiSelectColumn", each if _ = null then null else Text.Combine(_, ", ")}}
)

Swap “YourCountryColumn” for your actual column name. The null check stops errors on empty cells.

I skip expanding lists to separate rows unless absolutely necessary. Creates too many duplicate records and makes dashboards messy.

Test on a small dataset first. Airtable API gets weird with large multi-select data.

5

Airtable multi-selects in Power BI drove me nuts for weeks until I cracked it. The problem? Airtable sends multi-select fields as JSON arrays, and Power Query sees them as lists but won’t convert them to readable text.

Your pagination and expansion logic looks good, but you need one more step. After “Expanded Field Data”, add this to handle all list columns:

#"Handle Multi-Select Lists" = Table.TransformColumns(
    #"Expanded Field Data",
    List.Transform(
        Table.ColumnsOfType(#"Expanded Field Data", {type list}),
        each {_{0}, each if _ <> null then Text.Combine(_, ", ") else null}
    )
)

This finds any list columns and turns them into comma-separated text. I like this approach because it adapts when your schema changes - no manual column names needed.

Heads up: some Airtable linked record fields also come back as lists. This code handles both multi-selects and linked records, but watch for deeply nested stuff in complex bases.

6

Same thing happened to me. Add a transform step after expanding that catches list columns automatically. Use Table.TransformColumns with Text.Combine - it’ll convert those arrays to readable text. I stick with comma separators, but switch to semicolons if your Airtable data already has commas. Skip expanding each value to separate rows unless you absolutely need it - it’ll bloat your dataset and kill performance.