Extracting Data from Airtable to Power BI - Some Columns Appear as Lists and Data is Unavailable

Other Questions Airtable
1

Problem Statement

I am encountering a challenge when importing data from an Airtable view into Power BI. Specifically, columns that contain multiple values (like multi-select fields) are displayed as “List” in Power BI. For instance, a column labeled “Country” in Airtable, which has options like Indonesia, Philippines, Qatar, etc., shows up simply as “List” rather than listing the countries individually in Power BI.

Additional Information

It appears that all other columns import correctly, but I am seeking a straightforward solution to retrieve these multi-select fields effectively.

I’ve utilized PowerQuery for data extraction from Airtable, and here’s a revised version of the code I’m using:

let 
    // Pagination Logic to retrieve data from Airtable
    Results = List.Skip(
        List.Generate(
            () => [NextPage = "start", IterationCount = 0],
            each [NextPage] <> null,
            each [
                NextPage = try if [IterationCount] < 1 then "" else [ApiResult][Value][pagination] otherwise null,
                ApiResult = try if [IterationCount] < 1 then
                    Json.Document(
                        Web.Contents(
                            "https://api.airtable.com",
                            [
                                RelativePath = "v0/" & BASE & "/" & TABLE & "?view=" & VIEW,
                                Headers = [Authorization = "Bearer " & API_TOKEN]
                            ]
                        )
                    )
                else
                    Json.Document(
                        Web.Contents(
                            "https://api.airtable.com",
                            [
                                RelativePath = "v0/" & BASE & "/" & TABLE & "?view=" & VIEW & "&pagination=" & [ApiResult][Value][pagination],
                                Headers = [Authorization = "Bearer " & API_TOKEN]
                            ]
                        )
                    ),
                IterationCount = [IterationCount] + 1
            ],
            each [ApiResult]
        ),
        1
    ),

    // Transforming the data into a table
    ToTable = Table.FromList(
        Results, Splitter.SplitByNothing(), null, null, ExtraValues.Error
    ),

    // Expand and structure data fields
    ExpandData = Table.ExpandRecordColumn(
        ToTable, "Column1", {"Value"}, {"Value.Column"}
    )
// Further processing of data can be done here
in
    ExpandData

I would greatly appreciate any guidance or suggestions, as I’m not very experienced with this process and rely heavily on community insights. I’ve explored multiple solutions, including creating a new column in Airtable, but haven’t found a satisfactory workaround. Please be as detailed as possible in your responses. Thank you for your assistance!

2

One approach that may solve your problem involves transforming those “List” values at the PowerQuery stage in Power BI. You can consider using the ‘Transform Column’ option to convert lists into text strings within PowerQuery itself. Select the column, then choose ‘Transform’ and opt for ‘Extract Values’ which allows you to select a separator like a comma. This will convert list entries into a delimited string, making it more readable and usable in visuals or reports within Power BI.

3

hey, have u tried using DAX in powerbi to transform those lists into text? sometimes using heroes like CONCATENATEX function can work wonders to transform lists into readable strings. give it a shot, it might just do d trick for ur tables! good luck!