How to update my dashboard for a monthly report from an Excel SharePoint file ?

[Anonymous]

I have multiple dashboards.

I Get Data from a SharePoint file that is an Excel workbook.

It needs to be updated monthly.

How can I import the new workbook without creating a new query and configuring all the dashboardson where they pull the data from ?

I have dashboards that Get Data from Sharepoint file that's an Excel workbook in July...

Now I need to get updated data from August...not add to the previous month's data...replace the data source.

The monthly reports are store in a SharePoint location.

I use the SharePoint URL for the Gat Data > SharePoint file data source.

The table name that needs to be updated is Monthly Billing.

The columns have the same name for every report.

The reports come in with a different name.

[Migasuke]

Hi @Anonymous ,

there are two options how to solve your problem:
1. You will keep only one file on your SharePoint and you replace values directly in this file - and whenever you refresh your report, your data will be updated. This is probably easiest for you at this moment.

2. You will store all Excel Workbooks in a SharePoint Folder , for example June, July, August.... and you will use connection in PBI based on SharePoint Folder. This connection will enable to connect to all files, you have stored there, but also you will have a posibillity to apply dynamic filter in Power Query, that only latest file is used.

I hope this will help you.

[VijayP · Accepted solution]

@Anonymous 

First thing I need to mention here , you are dealing with Reports not Dashboard.
(coz dashboards are created in service  from reports not from the source data)

1. If you want to replace previous month report with current month report, you need to replace the Data source (rows) with latest Data , and refresh pbix file in desktop to take the changes and gives you the latest report.

2. you need to setup an onpremise gateway to handle the schedule refresh to make sure your reports are published time to time based on the intervels you have setup .

Let me know if you are seeking this solution !

[Anonymous]

For Step 1., where do I make that change exactly ?  Could you provide me the steps to accomplish this ?

[v-kkf-msft]

Hi @Anonymous ,

You can use the PowerBI Desktop or Rest API to change datasource.

1. Open the Advanced Editor in Power Query Editor, then update the Excel name, like this:

2. Use Power BI Rest API.

Datasets - Update Datasources In Group 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.