cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frixel
Post Prodigy
Post Prodigy

Problem to combine multiple files with different sheet names.

Hi All,

 

I have a problem with combine multiple files from Sharepoint folder.

 

Al the files are in one folder and have all the same name and colomn name/format.

But each file, the tab also has the name of the file and so they do not match with all files and an error occurs in Power Bi.

 

I have found this treath;

https://community.powerbi.com/t5/Power-Query/Combine-all-sheets-with-different-names-from-multiple-f...

on this fantastic form😉 . There is the code for to resolve this problem.

But mayby somebody tell me in with order and where i must set it in?

 

1.png

 

1 ACCEPTED SOLUTION
AilleryO
Solution Sage
Solution Sage

Hi,

 

Did you load your file, using connection Folder or Excel ?

If you create a connection to a folder, you should have a function (fx), parameters, and a query in the left panel.

 

The solution you pointed out is in M code (Power Query) and need to be copy and paste on the formula bar (new steps) or in the advanced editor.

 

But I can offer you a more simple solution, if...

you always need the first sheet  (1st sheet = 0, 2nd=1...) of your Excel files, you can also use :

#"Sales_Sheet" = Source{0}[Data]

instead of

#"Sales_Sheet" = Source{[Item="Sales Week 32",Kind="Sheet"]}[Data]

 

Hope this helps you, let us know

View solution in original post

4 REPLIES 4
AilleryO
Solution Sage
Solution Sage

First open Power Query Editor

You need to find the step where your Excel sheet is called by its name, the step with the Source function.

It can be in your query or maybe in a function depends on your connection.

Or look directly in advanced editor and find a line with Source{[Item="nameofyourfile",Kind="Sheet"]}[Data]

and replace it with :

Source{0}[Data]

Without your query i cannot tell you where it is, but you'll find it 🙂

AilleryO
Solution Sage
Solution Sage

Hi,

 

Did you load your file, using connection Folder or Excel ?

If you create a connection to a folder, you should have a function (fx), parameters, and a query in the left panel.

 

The solution you pointed out is in M code (Power Query) and need to be copy and paste on the formula bar (new steps) or in the advanced editor.

 

But I can offer you a more simple solution, if...

you always need the first sheet  (1st sheet = 0, 2nd=1...) of your Excel files, you can also use :

#"Sales_Sheet" = Source{0}[Data]

instead of

#"Sales_Sheet" = Source{[Item="Sales Week 32",Kind="Sheet"]}[Data]

 

Hope this helps you, let us know

@AilleryO 

 

Thanks for your help and i find out how to do.
Also with this youtube movie.
https://www.youtube.com/watch?v=JZSdAvGAq_E

@AilleryO 

 

I use the sharepoint folder connector  like this.

 

1.png

And the code you give, wher do i paste that in?

Is that also in the formule bar?

like here?

2.png

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors