Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
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.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Find out what's new and trending in the Fabric Community.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.