Multiple monthly excel files

[Anonymous]

Hi,

Was hoping for some guidance. Trying to create a dashboard which connects to two separate excel workbooks, each with multiple tabs. However monthly two additional excels are created by the finance department. This data from these needs to also be added (not replace) to the data from the previos months excel workbooks. The templates used on these excel workbooks is always the same, where no additional columns are added, as they are produced using a standardised template. Additionally these excels are hosted on an internal public onedrive folder.

1) Is there a way to set up a dashboard to update when ever new files are available, or manually triggered by the user when ever a new file is added or finally a schedualed procedure?

2) Also is there a way to ensure that the additional data added is prefixed with some of the name of the new file?

[v-yuezhe-msft · Accepted solution]

@Anonymous,

If you put all the Excel files in OneDrive for business folder, you can enter the following URL in SharePoint Folder connector of Power BI Desktop to get data from all the excel files .

https://mydomain-my.sharepoint.com/personal/user_mydomain_com

In Power BI Query Editor, filter the folder path to your folder(the folder is named "test" in my scenario ), then expand the content column. You can see that all the data are prefixed with excel name in second screenshot.

filter folder path and expand content columndata are expanded from all the excel files

After that, you can create report in Power BI Desktop, publish the report to Power BI Service and set schedule refresh for the dataset. This way, when you add new excel files to onedrive for business folder, new data will be automatically added to Power BI Report based on refresh schedule .

Regards,
Lydia

[zaheerahmed]

My situation is similar however the data is hosted locally on my computer. My issue however is that each month I get a new excel workbook with the "new month's data" but I don't want to go through the cleaning process.

So for example spreadsheet 1 comes with data which I clean in PowerBI, import, update field types etc. - now I create my report with various visualisations for this month.

In month 2 I have a new spreadsheet with the exact same data layout (spreadsheet 2). What is the most effecient way to import this into PowerBI (and avoid redoing the cleaning, etc) and update my report with the fields in the new report without having to manually edit each visualisation and drag the relevant field to each visualisation?