Forum Discussion
how to create a query that paginates?
Community ChampionPls check this code:
let
Pagination = List.Skip(List.Generate( () => [Last_Key = "20170404130408053410572", Counter=0], // Start Value
each [Last_Key] <> null and [Last_Key] <> "", // Condition under which the next execution will happen
each [ Last_Key = try if [Counter]<=1 then "20170404130408053410572" else [WebCall][lastKey] otherwise null,// determine the LastKey for the next execution
WebCall = Json.Document(Web.Contents("https://apiv2.clickmeter.com/datapoints/8697350/hits?timeframe=last30&limit=10&offset="&Last_Key&"%408693934&authKey=fde74f69-ea93-411f-96b2-5eb9cb4c0993")), // retrieve results per call
Counter = [Counter]+1// internal counter
],
each [WebCall]
),1),
#"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column3" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"hits"}, {"hits"}),
#"Expanded hits2" = Table.ExpandListColumn(#"Expanded Column3", "hits"),
#"Expanded hits3" = Table.ExpandRecordColumn(#"Expanded hits2", "hits", {"id", "accessTime", "entity", "browser", "os", "location", "conversions", "type", "ip", "isSpider", "isUnique", "trackedParameters"}, {"id", "accessTime", "entity", "browser", "os", "location", "conversions", "type", "ip", "isSpider", "isUnique", "trackedParameters"})
in
#"Expanded hits3"
let
Pagination = List.Skip(List.Generate( () => [Last_Key = "init", Counter=0], // Start Value
each [Last_Key] <> null, // Condition under which the next execution will happen
each [ Last_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/ID/Audit?api_key=KEY")) else Json.Document(Web.Contents("https://api.airtable.com/v0/ID/Audit?api_key=KEY&offset="&Last_Key&"")), // retrieve results per call
Counter = [Counter]+1// internal counter
],
each [WebCall]
),1),
#"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"HasError", "Value"}, {"Column1.HasError", "Column1.Value"}),
#"Expanded Column1.Value" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.Value", {"records", "offset"}, {"Column1.Value.records", "Column1.Value.offset"}),
#"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"}),
#"Expanded Column1.Value.records.fields" = Table.ExpandRecordColumn(#"Expanded Column1.Value.records1", "Column1.Value.records.fields", {"Result id", "Question", "Item", "Report id", "Result", "Zero tolerance", "Comment"}, {"Column1.Value.records.fields.Result id", "Column1.Value.records.fields.Question", "Column1.Value.records.fields.Item", "Column1.Value.records.fields.Report id", "Column1.Value.records.fields.Result", "Column1.Value.records.fields.Zero tolerance", "Column1.Value.records.fields.Comment"}),
#"Extracted Values" = Table.TransformColumns(#"Expanded Column1.Value.records.fields", {"Column1.Value.records.fields.Question", each Text.Combine(List.Transform(_, Text.From)), type text}),
#"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"Column1.Value.records.fields.Report id", each Text.Combine(List.Transform(_, Text.From)), type text}),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Values1",{{"Column1.Value.records.fields.Result", Int64.Type}, {"Column1.Value.records.fields.Zero tolerance", type text}})
in
#"Changed Type"
This snippet of code works perfectly for fetching data from Airtable and working with it in the Power BI Desktop app. As well as publishing to online. However, when I try to automate data refresh through the online interface Power BI lets me know it's not supported even though it clearly is a Web data source:
"You can't schedule refresh for this dataset because the following data sources currently don't support refresh:"
let
Source = Json.Document(Web.Contents("https://api.airtable.com/v0/ID/Audit?api_key=KEY")),
records = Source[records],
#"Converted to Table" = Table.FromList(records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "fields", "createdTime"}, {"Column1.id", "Column1.fields", "Column1.createdTime"}),
#"Expanded Column1.fields" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.fields", {"Result id", "Question", "Item", "Report id", "Result", "Zero tolerance", "Comment"}, {"Column1.fields.Result id", "Column1.fields.Question", "Column1.fields.Item", "Column1.fields.Report id", "Column1.fields.Result", "Column1.fields.Zero tolerance", "Column1.fields.Comment"}),
#"Extracted Values" = Table.TransformColumns(#"Expanded Column1.fields", {"Column1.fields.Question", each Text.Combine(List.Transform(_, Text.From)), type text}),
#"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"Column1.fields.Report id", each Text.Combine(List.Transform(_, Text.From)), type text}),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Values1",{{"Column1.fields.Result", Int64.Type}})
in
#"Changed Type"
If I redo the web connection and specify my column structure again I get the same data but only limited to 100 rows. This time online refresh is supported though.
Any ideas on how we could work around this? Or how to specify the code below to be online refresh compatible?
in your web call - in second part(else part) you are giving a variable inside web.contents - which is not supported in power bi service for scheduled refresh.
Thanks to excellent blog by Chris Webb - this can be solved - or lets say there is a way to overcome this issue.
In your case, you would need to prvide a fixed value here instead of "&Last_Key&"
"https://api.airtable.com/v0/ID/Audit?api_key=KEY&offset="&Last_Key&""))
with query paramters as folow
Web.Contents("https://api.airtable.com/v0/ID/Audit?api_key=KEY&offset=someFixedValue",Query=[offset=Last_Key])This someFixedValue has to be some valid value which works fine - say your first value of key - which will be used as a dummy value only to 'trick' the PBI service.
BR
emudria.
I got it to work by adding extra [] brackets.
Json.Document(Web.Contents("https://api.airtable.com/v0/ID/Audit?api_key=KEY&offset=0",[Query=[offset=Last_Key]])),Thanks!
--- OLD POST ---
Thanks for getting back to me!
I've tried your formula but am still getting errors.
WebCall = try if [Counter]<1 then Json.Document(Web.Contents("https://api.airtable.com/v0/ID/Audit?api_key=KEY")) else Json.Document(Web.Contents("https://api.airtable.com/v0/ID/Audit?api_key=KEY&offset=0",Query=[offset=Last_Key])), // retrieve results per callBasically the first 100 rows (when counter <1) always load. But then regardless of what fixed value I set, even with no fixed value, the 101 row returns an error.
Likewise, if I input the URL in the browser offset=0 returns values (probably the same 100 first that's also loaded into Power BI) and anything else I've tried >0 returns error. If I set offset=1, I get "{"error":{"type":"INAVLID_OFFSET_VALUE","message":"The value of offset 1 is invalid"}}