cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
twister8889
Helper V
Helper V

Get Data from Multiple Excel Files with multiples Sheet and separate each sheet by table

Hi guys,

 

I need help to implement the load by multiples excel files from folder/SharePoint. I have a folder that contains a lot of excel file, all the excel files have the same structure (The same sheet quantity: sheet1, sheet2, sheet3, and sheet4; the same struct sheet, the same sheet name, but each sheet is different from each other). So I need to load theses excel files, after that, I need to create the columns by excel file name (because the excel name will be the filter into the dashboard). I would like to create tables into the model for each sheet, for example, one table to sheet1, sheet2, ...

 

Basically the problem is: How to load theses excel files into power bi and separate, to each sheet be the table and use the excel name as a column for each table?

 

Here are excel files: https://1drv.ms/u/s!AoDYwrtLrltJn1AmP9R-vWOWRP_w?e=3XqaKQ

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@twister8889 - I think you will need to have a Folder query for each of your sheets that you want as a table. @ImkeF or @edhans might know better.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

3 REPLIES 3
edhans
Super User
Super User

You are going to have to do this 4 times. You can get all sheets in all excel files at once by clicking on the "parameters" folder once you click on combine. That will get all 4 sheets. But, your sheets are not in the same format, so any further transformations will have to be custom. Sheet 1 is the same in files 1 and 2, but Sheet 2 is not the same as sheet 1. So each pair needs its own transformation.

edhans_0-1599575733642.png

To get the sheet name, you need to make sure that the "Remove Other Columns1" step in the Combine operation retains the Source.Name column.

edhans_1-1599575983040.png

I do not understand your question though "... each sheet be the table and use the excel name as a column for each table." Each sheet as a table is easy in the combine operation. Do Sheet 1 first, then repeat the combine operation for 2, 3, and 4. But what do you mean use the Excel name as a column for each table exactly? If you mean have a column with the file name, then my direction above will do that.

If you mean something else, please clarify.

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Greg_Deckler
Super User
Super User

@twister8889 - I think you will need to have a Folder query for each of your sheets that you want as a table. @ImkeF or @edhans might know better.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

amitchandak
Super User
Super User

@twister8889 , you should be able to load like

https://powerbi.microsoft.com/en-us/blog/combining-excel-files-hosted-on-a-sharepoint-folder/

and get new data

https://medium.com/@wkrzywiec/getting-data-from-the-latest-file-in-a-folder-using-power-query-51dfa4...

 

Now if need table for each table that would be like load one excel create the model and the get all other data appended.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.