Forum Discussion

ROKRI's avatar
ROKRI
Helper I
7 years ago
Solved

Airtable - Certain Columns Pulled in as Lists, Cannot Get the Data

Hello Power BI Community,

I need your help please to address an issue when importing data from Airtable where columns based on an array (data pulled from other tabs within Airtable) only show up as "List" once imported to Power BI. When I expand the column it only returns nonsense. 

Image of issue below, see "Country":

 

 

 

The issue affects all columns from Airtable that pull data from other sheets within the Airtable environment, as opposed to direct entry or drop downs, which work fine. Here is the specific column description for "Countries" from Airtable API page: 

"array of record IDs (strings). Array of linked records IDs from the Countries table. The order of record IDs will reversed compared to what you see in the app."

 

Please see below the Power Query I use if it helps (thanks to Matthew_Billiodeaux1 from Airtable Community Post (https://community.airtable.com/t/airtable-link-to-excel-via-power-query/18675). It pulls all data minus data for the columns in question (below is a cut and paste of the query, sorry if it's unreadable, link above if needed)

 

let Pagination = List.Skip( List.Generate( () => [Page_Key = "init", Counter=0], // Start Value each [Page_Key] <> null, // Condition under which the next execution will happen each [Page_Key = try if [Counter]<1 then "" else [WebCall][Value][offset] otherwise null, // determine the LastKey for the next execution WebCall = try if [Counter]<1 then Json.Document(Web.Contents("https://api.airtable.com/v0/[PartialAddressToYourBase]/[NameOfYourTable]", [Headers=[Authorization="Bearer keyXXXXXXXXXXXXXX"]])) else Json.Document(Web.Contents("https://api.airtable.com/v0/[PartialAddressToYourBase]/[NameOfYourTable]?offset="&[WebCall][Value][offset] , [Headers=[Authorization="Bearer keyXXXXXXXXXXXXXX"]])),// retrieve results per call Counter = [Counter]+1// internal counter ], each [WebCall] ), 1 ), #"Json2Table" = Table.RenameColumns(Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),{{"Column1", "stepA.1"}}), #"Expanded to stepA.2" = Table.ExpandRecordColumn(#"Json2Table", "stepA.1", {"Value"}, {"stepA.2"}), #"Expanded to stepA.3" = Table.ExpandRecordColumn(#"Expanded to stepA.2", "stepA.2", {"records"}, {"stepA.3"}), #"Rows from stepA.3" = Table.RenameColumns(Table.ExpandListColumn(#"Expanded to stepA.3", "stepA.3"),{{"stepA.3", "stepB.1"}}), #"Source" = Table.ExpandRecordColumn(#"Rows from stepA.3", "stepB.1", {"fields"}, {"Src"}) in #"Source"

 

Please be as specific as possible in your response as I am NOT an expert at any of this and rely on cut and paste, as well as this community, for complicated forumlas. Sorry if my terminology is off. I appreciate your guidance and ideas.

 

Thanks to the community, I have figured out how to import data with more than 100 rows from Airtable, as well as how to expand into a full table with all columns. However, and after much searching, I can't get past this "List" issue as it affects numerous columns. 

 

Thank You!!

 

ROKRI

  • Now I got it: As they are dropdown fields and just allow you to select existing/defined items, the returned values will be their keys. So you have to merge them with the respective tables to lookup their values from those tables.

10 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar

    Hi ROKRI ,

    I cannot see your data from your link due to security.

    If I understand your scenario correctly that you have problems when you expend List in the filed?

    If it is convenient, could you share the screenshots about the result when you expend the list?

    In addition, may be ImkeF  will help you who is good at M query.

    Best Regards,

    Cherry

     

    • ImkeF's avatar
      ImkeF
      Community Champion

      Sorry, but don't understand the problem yet. 

      What do you see if you expand a list column by clicking on the arrrow-button?:

       

    • ROKRI's avatar
      ROKRI
      Helper I

      Thank you v-piga-msft  and ImkeF for your response. Please pardon delayed response from me as I have been out of office.

       

      Sorry I was not clear and hopefully this will help clarify. 

       

      What Works

      I am able to import data from Airtable (more than 100 rows) and expand columns to view collapsed cells. Airtable data that is direct cell entry, not drop down selection, comes through fine and I can expand and view all rows. 

       

      What's Not Working

      Data in Airtable populated by drop down options does not come through into Power BI as usable data. For example, in Airtable when you must select an option for data entry, such as "Country - options being Indonesia, Philippines, Qatar, etc.", it displays in Power Query Editor colmun "Country" as hyperlinked word "List" for each entry (same as image ImkeF sent). Then, when I click the double arrow in the "Country" column title to exand and view all rows, data for each row displays as "rec2mjnqbm20CLWEs" or similar. 

       

      I hope that helps and answers both of your questions. For some reason I'm not able to paste/attach additional images with this response, please advise if needed. 

       

      Standing by and Thank You,


      ROKRI

       

       

       

       

       

       

       

      • ImkeF's avatar
        ImkeF
        Community Champion

        That looks like a binary string, that PQ can handle. But it doesn't work as it is now. 

        Could you please check that there are no characters missing?