Refreshing data from gov.uk

[Sharksguts]

I am trying to setup a Power BI dashboard to link to milk price data here https://www.gov.uk/government/statistics/uk-milk-prices-and-composition-of-milk

I am downloading the the ods file and opening it within power bi by reading it as an excel file as I tried using the web source and got the following error.

DataFormat.Error: External table is not in the expected format.
Details:
9018bd50-02db-4f50-bb09-7d98bda3b6b3.XLS

Even if I got the ods file to read straight from the web the link on the .gov.uk looks to be date related so will change each month.

What I am trying to setup is a method that will update the data with no manual intervention required.

Any suggestion greatly appreciated.

Regards

Richard

[v-xjiin-msft]

Hi @Sharksguts,

I have checked the milk price data from your shared link. In the ods file, there exists some useless data like the Title or Updated on or Contact etc. These data will affect the format of your excel file and cause the error. To resolve the error, I think you have to remove these data manually.

And generally if we want to update data automatically, we'll use scheduled refresh. Please refer to following article:

Configuring scheduled refresh

Thanks,
Xi Jin.

[rakeshb]

Are there any data manipulations in the dashboard building?

[Sharksguts]

If I understand your question directly yes I manipulate the data slightly by promoting to headers and changing a column name and format.