Forum Discussion
Excel - Office 365 (Desktop)
If you mean scheduled refresh in Power BI or by Microsoft Flow, it updates only the data model and tables in master file will be updated if they are generated from data model (Pivot Table or reversed linked Table in the sheet with EVALUATE in behind). In this case Power Query on secondary file shall update data model in master file.
Hello Sergei.
I was definitely looking at it the wrong way. I reworked and rearranged all of the workbooks. I now have 2 secondary files, that are connected to the master file through queries, and one master file that is the one connected to Power BI. I previously had in the master file, several additional worksheets that were reading the data in the queries, and from there I did the formulas, which is why it wouldn't expand the table when new data was added. I now calculate the formulas directly in the worksheet that was created by the Power Query, so now when data is added in the secondary file, so it does in the master file. I also added the automatic refresh to the queries in the master file, to the updated every 30 min.
Now, I'm only missing for the master file to be updated when I make an on-demand refresh to the Power BI report/dashboard, without opening the master file and clicking refresh.
Is there any way to to this?
Thanks.
Power BI refresh, on-demand or scheduled one, updates only data model and what is based on it, not the tables returned by Power Query. You need present results taken from data model to be automatically updated.
To see if I understood, you're saying that I need the Power BI to be directly connected to the data (secondary files) and not to the master file I created with the additional data and formulas., right? But for Power BI to update and refresh the data, the file (Excel) can't be opened. How will I manage this without a Master File?
Thanks.
Not exactly. Let say you have master file placed on Sharepoint / Onederive and connected to Power BI workspace. Power Query in master file updates information from secondary files (doesn't matter where they are) and return information into the tables in master file. Let assume as well quires return data into the master file data model as well.
In this case Power BI refresh won't update returned by quires tables in the sheet and will updated data model only. To work with tables, don't use tables returned by quires, use queries as connections only. Instead return tables (so called reverse linked tables) into the sheet from data model as explained here https://www.sqlbi.com/articles/linkback-tables-in-powerpivot-for-excel-2013/ and build your formulas based on these tables.
In such case above tables in master file shall be updated by Power BI refresh.