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

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
Continued Contributor
Continued Contributor

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
Continued Contributor
Continued Contributor

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
Continued Contributor
Continued Contributor

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

@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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors