cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
crwinchester
New Member

Problem with Updated Data containing offset

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.

 

powerbi_offset_glitch.PNG

6 REPLIES 6
Super User IV
Super User IV

Re: Problem with Updated Data containing offset

Hi there

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

And then you can transform that to a table?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Power BI Blog
crwinchester
New Member

Re: Problem with Updated Data containing offset

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
New Member

Re: Problem with Updated Data containing 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?

Super User IV
Super User IV

Re: Problem with Updated Data containing offset

Hi there

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Power BI Blog
Community Support
Community Support

Re: Problem with Updated Data containing offset

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
crwinchester
New Member

Re: Problem with Updated Data containing offset

@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.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors