I have the following Excel Power Query:
let
Source = Sql.Databases("MyConnectionString.datamart.fabric.microsoft.com"),
MyDatabase = Source{[Name="MyDatabase"]}[Data],
MyExcelQuery = MyDatabase{[Schema="model",Item="MyExcelQuery"]}[Data]
in
MyExcelQuery
- I've removed SQL connection string links and DB references.
- The above is an accurate reflection of just *how brief* this query is
- The spreadsheet is located in Sharepoint and the above query is refreshed every data via Power Automate.
- Thus far there have been zero errors via Power Automate - the PQ statement refreshes and everything looks fine.
- However - additional data and columns that had been introduced into the upstream DataMart were not being downloaded via the Excel Power Query and showing up in the relevant Excel table. It's like the Excel PQ got stuck at a point in time and was refreshing but not actually doing anything.
What did I do?
- I cut/pasted the existing PQ statement (shown above) into a blank spreadsheet and hit "refresh"
- All the changes appeared
- This surprised me but is this something to do with cached data?
- I then cut/pasted the same PQ statement back into the original spreadsheet and hit "refresh" and - guess what?
- Yup - all new changes appeared!
- In effect I could have cut/pasted the PQ statement into the original PQ and hit refresh and have got the same result.
- Not sure why though!
Does anyone have any insights into this behaviour?
It's got be stumped and I could do with some helpful pointers to work out the root cause of the problem so that I can work around it.
