Get Started
Support Home

We’ve launched a new Help Center!

Visit the new Documentation space for Azure DevOps, monday.com, and Jira. Or, if you require any assistance or have any questions, please visit the new Customer Support Portal for Azure DevOps, monday.com and Jira.

Learn more

Errors in excel power query

Implemented
Follow
New post
Avatar
Benny Jamine

Yesterday I updated from excel 2016 to excel 365. Now when I try to update the data, I get errors. I don't know if this is coincidence or not.

1. When I open an existing file with a power query using the API, I get following error :

Edit Settings opens a dialog in which I connect change anything.

2. When I try to setup a new connection in a new file, using the same API URL, I get other errors:

or

 

2

Comments

17 comments

Sort by Date Votes
  • Avatar
    dev

    Hi Benny,

    Could you try to use this article? It should be helpful in your case:

    https://support.7pace.com/hc/en-us/articles/360001151106-What-should-I-do-about-the-error-I-m-getting-when-calling-the-reporting-API-

    Best regards,

    Sergey

    0
    Comment actions Permalink
  • Avatar
    Krzysztof Truong

    I have a PowerBI report utilizing TimeTracker data, configured to refresh every day. It's been failing to refresh since yesterday with the exact same error message.

    0
    Comment actions Permalink
  • Avatar
    John Dawes

    We've been experiencing this since yesterday in both Excel and PowerBI, and it's impacting our ability to issue TimeSheets to our customers (especially as it's month end). Clearly there's a regression in the 4.28 release, as our scheduled refresh started to fail at 8am on the 28th.

    0
    Comment actions Permalink
  • Avatar
    7pace tech support

    Hi everyone,

    Yes, I confirm, there is a regression in release 4.28.

    It looks like we identified the issue and we are preparing update with fix. We plan to rollout this within couple of hours.

    I will post update here.

     

    Maxim Lutsan

    0
    Comment actions Permalink
  • Avatar
    7pace tech support

    We rolled out update addressing OData issue, please let us know if it is working for you now.

    Error with "Invalid JSON" is not solved, as it is confirmed issue in Power BI and can be resolved by removing "Implementation=2.0" in the advanced query settings.

     

    Maxim Lutsan

    0
    Comment actions Permalink
  • Avatar
    Howard Perkins

    We are experiencing the same issue across a number of previously working Excel spreadsheets. Is there anything we need to do to recover from the update - is it necessary to rebuild the connection in each spreadsheet?

    0
    Comment actions Permalink
  • Avatar
    7pace tech support

    Howard,

    could you please try to hit Refresh button in one of the broken spreadsheets to force reload?

    There was confirmed issue with @odata.context value in the latest release and it was fixed earlier today, I suspect that Excel can cache wrong value for some time.

     

    Maxim Lutsan

    0
    Comment actions Permalink
  • Avatar
    Krzysztof Truong

    Refreshing the PowerBI datasets still yields the same error ("The key didn't match any rows in the table.") :(

    0
    Comment actions Permalink
  • Avatar
    Howard Perkins

    Maxim,

    Like Krzysztof has discovered, no amount of refreshes make any difference (in excel or PowerBI).

    I then tried to create a new connection from scratch in a new spreadsheet. Even following the steps outlined in your knowledge base article for the OP, I am unable to get past errors at some point. Quite confusingly, I managed to get to the stage where the data would preview quite happily in the Power Query Editor, but wouldn't load into the main worksheet. I also noticed the startDate and endDate are now nullable texts (the knowledgebase article is now out of date?).

     

    Currently stuck for ideas what to try next....

    0
    Comment actions Permalink
  • Avatar
    7pace tech support

    Hi everyone!

    We've identified an additional issue in our OData, and we are working on the fix right now, it will be released soon.

    I'll post an update here.

    We are very sorry for this inconvenience

    Regards,

    Andarth

    0
    Comment actions Permalink
  • Avatar
    7pace tech support

    We've released an additional fix for our OData feed. Please check if it fixes your issues. It should work seamlessly if you haven't made changes to your OData Query, but if you've made it - please recreate the connection to your Query
    We are very sorry for this inconvenience and please let us know if now everything works as expected.
    Best regards,
    Andarth

    0
    Comment actions Permalink
  • Avatar
    Krzysztof Truong

    I can confirm it is working for us now - PowerBI data refreshed successfully

    0
    Comment actions Permalink
  • Avatar
    Howard Perkins

    Yep - all of the ones tested so far have worked - both PowerBI and Excel. Thanks.

    0
    Comment actions Permalink
  • Avatar
    7pace tech support

    Thanks everyone for your updates!

    Andarth

    0
    Comment actions Permalink
  • Avatar
    Benny Jamine

    Well, the issue is solved for existing connections, but I still get the JSON error when I set up  a new connection in a new excel file.

    0
    Comment actions Permalink
  • Avatar
    7pace tech support

    Hi Benny,

    Please check this video, I guess this is what you have in new files:

    https://youtu.be/3laY7vbUFFw

     

    Maxim Lutsan

    0
    Comment actions Permalink
  • Avatar
    Benny Jamine

    Hi Maxim,

    That did the trick. I wasn't aware the workaround with the advanced query editor still had to be applied.

    Thanks for your fast response.

    Benny

    0
    Comment actions Permalink

Please sign in to leave a comment.

Powered by Zendesk