Can I use a specific value in my parameter list to make a query?

[bloxvalz]

Hi,

I need to create 6 distinct connections to pull data from AirTable databases. Each AirTable database is a little bit different.

Airtable's documentation for PowerQuery gives me a M formula which uses parameters for the API_KEY, the BASE_ID (i.e. the database), and TABLE_ID (tables/views in the database).

I have 6 TABLE_IDs. I created a single TABLE_ID parameter and added the 6 values as a list (see below). I want to control the M query by specifying WHICH of the 6 values in the TABLE_ID parameter list is used for the query.

i.e. instead of the paramter as TABLE_ID in the M query, provide TABLE_ID[1]. I can't find documentation for this.

Is this possible?:

[V-pazhen-msft · Accepted solution]

@bloxvalz 
I cannot see the TABLE_ID used in your codes. Basically you just replace the content with the parameter name. For example, I replace the 5 with "Period Days", the query result will change based on the parameter selection.

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[bloxvalz]

My TableID list is in the first post. The M query is in the second post. The M query has 2 places where the parameter is passed. 

I take it as I should build 6 separate queries and hardcode the values I want.

This will work but not what I was looking to do. I'll accept your solution. 

[bloxvalz]

FYI: here is the Airtable documentation including the query:
Sending Airtable data to Microsoft Power BI & Power Query – Airtable Support

let Pagination = List.Skip(List.Generate( () => [Last_Key = "init", Counter=0],

 each [Last_Key] <> null,

 each [

   Last_Key = try if [Counter]<1 then ""

   else

     [WebCall][Value][offset] otherwise null,

   WebCall = try if [Counter]<1

   then

   Json.Document(Web.Contents("https://api.airtable.com",

     [RelativePath="v0/"&BASE_ID&"/"&TABLE_ID&"?api_key="&API_KEY&""]))

   else

   Json.Document(Web.Contents("https://api.airtable.com",

     [RelativePath="v0/"&BASE_ID&"/"&TABLE_ID&"?api_key="&API_KEY&"&offset="&Last_Key&""])),

   Counter = [Counter]+1

 ],

 each [WebCall]

),1),

 #"Converted to Table" = Table.FromList(

   Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

 #"Expanded Column1" = Table.ExpandRecordColumn(

   #"Converted to Table", "Column1", {"Value"}, {"Column1.Value"}),

 #"Expanded Column1.Value" = Table.ExpandRecordColumn(

   #"Expanded Column1", "Column1.Value", {"records"}, {"Column1.Value.records"}),

 #"Expanded Column1.Value.records" = Table.ExpandListColumn(

   #"Expanded Column1.Value", "Column1.Value.records"),

 #"Expanded Column1.Value.records1" = Table.ExpandRecordColumn(

   #"Expanded Column1.Value.records", "Column1.Value.records",

   {"id", "fields", "createdTime"},

   {"Column1.Value.records.id", "Column1.Value.records.fields", "Column1.Value.records.createdTime"}),

 #"Renamed Columns" = Table.RenameColumns(

   #"Expanded Column1.Value.records1",{{"Column1.Value.records.id", "_airtableRecordId"},

   {"Column1.Value.records.createdTime", "_airtableRecordCreatedAt"},

   {"Column1.Value.records.fields", "_airtableRecordFields"}}),

 #"Reordered Columns" = Table.ReorderColumns(

   #"Renamed Columns",

   {"_airtableRecordId", "_airtableRecordCreatedAt", "_airtableRecordFields"}),

 #"Expanded Record Fields" = Table.ExpandRecordColumn(

   #"Reordered Columns", "_airtableRecordFields",

   Record.FieldNames(#"Reordered Columns"{0}[_airtableRecordFields]),

   Record.FieldNames(#"Reordered Columns"{0}[_airtableRecordFields]))

in

 #"Expanded Record Fields"