Excel 2019 Query Randomly Stopped Working (The column 'Day' of the table wasn't found.)

[mste]

We have a user that pulls 10 day forecast results from weather(DOT)com for multiple locations and every query has simultaneously stopped working around May 27, 2020. I have sample code referenced below. I can't post screenshots since I'm apparently not allowed to yet. Since nothing has changed on our end I am suspicious that IBM might have changed something on the website.


Note that this code used to work on Office 2016 and Office 2019. I tried Power BI Desktop and can pull the table there but the code it uses is incompatible with Excel.

Please Login or Register  to view this content.

[alansidman]

Please read the yellow banner at the top of this page on how to attach a file.

I am not versed in Power BI, but I believe that the language of Power BI is DAX and that Power Query uses Mcode. I don't believe that they are compatible.

[CK76]

FYI - Power BI uses M code for query. DAX is generally used to aggregate or otherwise analyse/transform data once loaded to data model by the query.
In Excel DAX is used in PowerPivot/PowerView.

Power BI is basically PowerQuery and PowerView (on steroids) rolled into one. With additional controls and features.

And yes. It looks like site probably changed something. It's using API to get json data and using it to fill the page with info (i.e. you can't scrape data using your url. As the info ins't filled until page is loaded).

Oddly enough, their 10 Day weather isn't 10 days. But 14 days + 1 (today).

Don't have time to go into transformation steps. But you can get the data by using something like below.

Please Login or Register  to view this content.

EDIT: For clarity and grammar.

[alansidman]

@CK76

Please Login or Register  to view this content.

Thanks for that clarification. Since I have never worked with Power BI, this makes it very clear.

[mste]

Interesting, I will try playing around with that and see if I can replicate what the user is used to getting. I'm not an Excel expert so M code is new to me. Thanks for the suggestion and I will post back if I need more help.

[mste]

I have been scratching my head about how to get the different variables to be combined as rows in a single table and have not managed to do it yet. Most of the resources I've found mention pulling in tables from other queries and files. I know the code is a mess. I can try to clean it up after I figure out how to get the formatting right.

Please Login or Register  to view this content.

[CK76]

You may find List.Generate useful (though I don't think it's necessary here, you can use List.Accumulate as well), since all lists in the JSON should have same number of rows (i.e. 15 day's data).

You can use Text.Combine to concatenate each list using delimiter. Convert the resulting list to table. Then split by delimiter.

https://docs.microsoft.com/en-us/pow.../list-generate
https://docs.microsoft.com/en-us/pow...ist-accumulate