cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Multiple monthly excel files

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?
1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Multiple monthly excel files

@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.

1.PNGfilter folder path and expand content column2.PNGdata 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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Moderator v-yuezhe-msft
Moderator

Re: Multiple monthly excel files

@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.

1.PNGfilter folder path and expand content column2.PNGdata 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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

zaheerahmed Frequent Visitor
Frequent Visitor

Re: Multiple monthly excel files

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?

 

 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors