Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ROKRI
Helper I
Helper I

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

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

10 REPLIES 10
v-piga-msft
Resident Rockstar
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

 

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.

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

 

 

 

 

 

 

 

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?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

 

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

I believe I am having the same problem. How do I merge them with the respective tables?

 

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

Hi I have a similiar question. I am using Airtable for a survey I am doing where one table has the stops I make with several columns that are linked to another table for tools I select based on where they purchased them(each column has a different purchasing source). The issue I have is whenever I expand the column into new rows(because PowerBi sees a list), it duplicates each record's info for each column I expand.

 

I'm wondering if there is a better way to set up my Airtable info to better popular the data into PowerBi without having so much extra data and duplicates? 

 

I can setup the relationship up to match the record id to the correct tool name but with 500 surveys and over 3k tools there is a ton of duplicate info and tens of thousands of records. 

 

Table 1- Stops

Record 1 has Person with city, state, age etc... and also has 10 linked column where I choose the tools he has based on where he purchased them. 

 

Table 2- Tool Database

Record 1 has model # and other info as well as the linked records showing which techs chose which tool. 

 

Whenever I expand a record it duplicate all the survey info name, city, state etc but will only have the 1 record inside of the column. This seems very inefficient and even though I can tell PowerBi to Count (Distinct) when doing charts, it can be confusing. Stops with multiple choices in columnStops with multiple choices in columnToolbox Database showing record id and person choices.Toolbox Database showing record id and person choices.

Powerbi duplicates.JPG

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors