Problem to combine multiple files with different sheet names.

[Frixel]

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?

[AilleryO]

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 · Accepted solution]

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

[Frixel]

@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

[Frixel]

@AilleryO 

I use the sharepoint folder connector  like this.

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

Is that also in the formule bar?

like here?

[AilleryO]

Hi,

The modification of code I gave you is usefull for a Folder Connection but it is not the same as you're using a SharePoint Folder Connection.

References are not the same and it is not working the same way.

I'm not sure if you can do it the same way with a SharePoint folder.

Maybe other members can help you with this ?

Let us know or do not hesitate to open a new discusssion as the issue is not the same.