Values are not updating from excel spreadsheet source

[Anonymous]

I've created a report in PowerBI based on an excel spreadsheet of FX rates. Below is my report in the PBI desktop service. However, when I refresh it, none of the values change. 

Here is the data in the 'Model' tab, with both queries shown. 

And finally here are some screenshots of the source spreadsheet, which I can't figure out how to attach.

As you can see, the intended usage is this. 

1. The 'Historical Rates' page will show the exchange rates for the last three months, based on TODAY(), so should update once daily. 

2. the Live Rates page will return the current exchange rate so should change whener the report is updated. 

However, currently, no updates happen to the live rates when I press 'refresh' in the Report tab on PBI desktop. I get this summary flash up. 

Refreshing the data like this

also doesn't work.

What has gone wrong here?

Kind regards,

T

[v-yalanwu-msft]

Hi, @Anonymous ;

Is your problem solved? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Why? Can't I have my spreadsheet update? 

Let's assume that I don't want to use exchange rates, and I want to refresh a spreadsheet instead. How would I do that? 

[PaulDBrown]

Have you tried refreshing the whole model?

[Anonymous]

yes, it doesn't change the values

[PaulDBrown]

Another long shot....Try making a table out of the rogue cells (select columns & rows and ctrl +T). Use the table (instead of the sheet) on import. EDIT (forget this...it removes the functionality....)

and at the risk of stating the obvious, save the file before refreshing...

EDIT: I've just tested the import and it seems to work on my side:

[Anonymous]

So, opening the file, saving it and then refreshing worked. But how can I make it so that refresh updates those values automatically? 

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

---

@Anonymous wrote:

So, opening the file, saving it and then refreshing worked. But how can I make it so that refresh updates those values automatically? 

---

You could publish the .pbix to service then Configure scheduled refresh;

Power BI limits datasets on shared capacity to eight daily dataset refreshes.

You also could select OneDrive refresh，Power BI checks about every hour if a dataset connected to a file on OneDrive or SharePoint Online requires synchronization.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulDBrown]

Worth posting the question in the Power Automate forum:

https://powerautomate.microsoft.com/es-es/ 

[PaulDBrown]

You can probably automate the opening -> refreshing -> saving of the Excel file using VBA or Power Automate.

I once used VBA to do just this centuries ago but I'm no VBA expert by a long shot...but Goolgle was my friend then, so...

[v-yalanwu-msft]

Hi, @Anonymous ;

You need to get a exchange rate table  from some website.

About how to get a   Exchange Rate Conversion table ,you can refer to these related blog and video.

Exchange-Rates-best-website-to-use-for-Historical-data

multiple-currency-logic-in-power-bi-lookupvalue-example 

Handling Multiple Currencies in Power BI w/DAX 

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.