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
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
Memorable Member
Memorable Member

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

5 REPLIES 5
AilleryO
Memorable Member
Memorable Member

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
Memorable Member
Memorable Member

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

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.

 

Helpful resources

Announcements
Europe Fabric Conference

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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