Problem with Updated Data containing offset

[crwinchester]

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.

[GilbertQ]

Hi there

You should be able to click on the List, which will then expand.

And then you can transform that to a table?

[crwinchester]

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?" 

[crwinchester]

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?

[v-lid-msft]

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,

[crwinchester]

@v-lid-msft ,

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.

[GilbertQ]

Hi there

I would look into the data source as that is possibly affecting the data processed!