Forum Discussion
How to update a sheet that is the data source for reports with a newer version of source data
Hey folks.
This is likely a dumb question as I am trying to save repeated work.
Every week, I get a SQL Server report in Excel format. It has formatting that is problematic and the column names and data categories need some massaging to use in PowerBI.
I do have a dataset in PowerBI that is linked to an Excel workbook. I am looking for a simple way to update the PowerBI linked workbook with the new or changed data in the latest SQL report other than opening the SQL report and copying the new rows into the PowerBI linked workbook.
I am not at this point able to make a permanent link to the SQL Query that produces the report, when I can this problem goes away, although I may need to create a new dataset in PowerBI one time to make the dashboards work again.
Any thoughts on how to compare the latest SQL report which comes in Excel and use it to update the existing PowerBI linked Excel sheet will be sincerely appreciated.
Thank you
1 Reply
Not sure what do you mean exactly under dataset in PowerBI that is linked to an Excel workbook. Excel workbook is generated as SQL report.
Do you have another Excel file published on Power BI Service which query file with report? Or that's data model queries the report file? Or how it works exactly?
In general you may publish SQL report on some folder on Sharepoint online or OneDrive, power query it from another Excel file also published on sharepoint and be sure result of the query is loaded to data model. From Power BI workspace connect that file (not import) and schedule refresh. Resulting data is to be returned to Excel sheet by Pivot Table or by linkedback tables from data model.
With that updates will be more or less automatic.
