Forum Discussion
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.
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
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?
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 SrcThanks,
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.