cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ROKRI Frequent Visitor
Frequent Visitor

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":

Column as List Issue.PNG

 

 

 

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][o...] , [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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

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

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




8 REPLIES 8
Community Support Team
Community Support Team

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

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

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

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

Sorry, but don't understand the problem yet. 

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

 

image.png

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




ROKRI Frequent Visitor
Frequent Visitor

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

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

 

 

 

 

 

 

 

Super User
Super User

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

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?

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




ROKRI Frequent Visitor
Frequent Visitor

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

Thank you @ImkeF. The query actually works and pulls most of the data I need. The only data challenge are with the columns within Airtable populated based on drop down lists. 

 

I'm no query expert so knowledge is limited. I reviewed the query and since it works for most of the data, I see no reason why it won't grab the drop down option columns. From my limited knowledge, columns I need are listed as last part of query, bearer key is correct (though I changed for posting). Perhaps it needs another "expanded to stepA.4", middle part of query? Though I really have no idea.

 

Please see query below if it helps. I greatly appreciate your continued support: 

 
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/appeLOlVFTEAhg1NR/Opportunities", [Headers=[Authorization="Bearer keydXXXXXXXXXXXXX"]]))
			                                   else  Json.Document(Web.Contents("https://api.airtable.com/v0/appeLOlVFTEAhg1NR/Opportunities?offset="&[WebCall][Value][offset] , [Headers=[Authorization="Bearer keyXXXXXXXXXXXXX"]])),// 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"}),
    #"Expanded Src" = Table.ExpandRecordColumn(Source, "Src", {"Autonumber", "Opportunity Title", "Project Duration ", "Total Opportunity Value (USD)", "Currency", "Country", "Proposal Lead", "Donor", "Prime", "Comments", "Win Probability", "Go/No-Go", "Year", "Status", "Submission Deadline (New)", "Start Date", "End Date", "PD/TAD LOE", "Surge Support", "Proposal Type", "Region", "Reference Number", "US/UK", "Country Code", "ICR", "# Mths", "Country Risk Rating", "Cost Lead", "Proposal Team"}, {"Src.Autonumber", "Src.Opportunity Title", "Src.Project Duration ", "Src.Total Opportunity Value (USD)", "Src.Currency", "Src.Country", "Src.Proposal Lead", "Src.Donor", "Src.Prime", "Src.Comments", "Src.Win Probability", "Src.Go/No-Go", "Src.Year", "Src.Status", "Src.Submission Deadline (New)", "Src.Start Date", "Src.End Date", "Src.PD/TAD LOE", "Src.Surge Support", "Src.Proposal Type", "Src.Region", "Src.Reference Number", "Src.US/UK", "Src.Country Code", "Src.ICR", "Src.# Mths", "Src.Country Risk Rating", "Src.Cost Lead", "Src.Proposal Team"})
in
    #"Expanded Src

Thanks,

 

ROKRI

 

Super User
Super User

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

Code looks OK, as far as I can tell without actually seeing the data that is returned.

I believe that the returned data for your dropdown-fields is encoded and one has to find the correct encoding keys.

Otherwise error-would be shown.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Highlighted
Super User
Super User

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

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




ROKRI Frequent Visitor
Frequent Visitor

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

This is fantastic @ImkeF , thank you!

 

Took me a bit to figure out how to merge the tables. Here's how I got it to work based on what I understood, curious if this is what you meant - I pulled into Power BI from Airtable the additional tables from which the initial one pulls drop down options. Then, using the "Manage Relationships" in Power BI, I was able to setup a one to many relationship between the Airtable data and it worked!

 

I appreciate your support and patience. Thank you for helping me solve this issue.

 

ROKRI