Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I've been making edits to a data source and attempting to refresh in Power BI. The last time I refreshed I received a bunch of errors so I've been trying to removed applied changes to the query to repair the problem. Unfortunately, there is a new row called "offset" and now I'm no longer able to expand the record which effectively ruins the whole report! How can I remove the offset row so that I can get my data back? Thanks.
But how does it happen? The offending "offset" appeared after I made updates to the data source, which amounts to simply more rows from more dates in time. When the mysterious "offset" appears it breaks all the charts I have and I have to redo all of the applied change. What is an "offset?"
Furthermore, one-third of my data is omitted. I have close to 450 rows, but Power BI is only allowing 100 rows to come through. Why is this happening? Is it because of the offset glitch? Or is this limitation baked into Power BI?
Hi @crwinchester ,
If you are using a Web connector to get data from a RESTFul API , the service provide may let an API pull data from 100 records at a time and they will return a offset value with the records, you can use this value to get another 100 records until you have got all the data you want, please also refer to the following simiar thread: https://community.powerbi.com/t5/Desktop/Trouble-with-Pagnation-for-Airtable/m-p/817672#M393029
Best regards,
Thank you for sheding some light on this issue. I went through the post that you linked as well as the other post linked within that post and tried the code (with relevant web ids and api keyts) in the advanced editor to no avail.
This code returns this error: "An error occurred in the ‘’ query. Expression.Error: The name 'Pagination' wasn't recognized. Make sure it's spelled correctly."
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/<APP_ID>/<TABLE_ID>?api_key=<KEY_ID>")) else Json.Document(Web.Contents("https://api.airtable.com/v0/<APP_ID>/<TABLE_ID>?api_key=<KEY_ID>&offset="&Last_Key)), // retrieve results per call
Counter = [Counter]+1// internal counter
],
each [WebCall]
),1)
And this code returns this error: "Expression.SyntaxError: Token Identifier expected"
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 [Quelle][Value][offset] otherwise null, // determine the LastKey for the next execution
Quelle= try if [Counter]<1 then
Json.Document(Web.Contents("https://api.airtable.com/v0/<APP-ID>/<TABLE NAME>?view=<VIEW_NAME>", [Headers=[Authorization="Bearer YOUR_API_KEY"]]))
else
Json.Document(Web.Contents("https://api.airtable.com/v0/<APP-ID>/<TABLE NAME>?view=<VIEW_NAME>&offset="&Last_Key, [Headers=[Authorization="Bearer YOUR_API_KEY"]])), // retrieve results per call
Counter = [Counter]+1 // internal counter
],
each [Quelle]
),1),
.....
.....
in
Any other suggestions on how to overcome this issue? Thanks again.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |