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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Frixel
Post Prodigy
Post Prodigy

How combine multiple files from folder NO table

Hi all,

 

I have troubles with read files from a folder and to combine.

in the folder are files with tables but also files without tables.

 

This is the error i alway`s have.

Frixel_0-1641396500458.png

 

What can i do so the data is correct loaded?

1 ACCEPTED SOLUTION

and after it should be like this :

ChangeSheetName_after.png

Do not forget that the most left is 0, then 1. So you might have to change the number if your data are not on the most left sheet of your files.

 

You might have to add a step to promote headers but not always the case. Just check if your headers are in place otherwise add a step after Navigate to promote headers.

 

Tell us if it works...

View solution in original post

8 REPLIES 8
AilleryO
Memorable Member
Memorable Member

Hi,

 

In your M code (Advanced editor, Power Query), you change just a little part of the code to "call" the sheet by its order number and not its name. So it won't be a problem even if your sheets in your files and in your folder have not the same name but they must be at the same place (in the same order).

 

Select the query called "transform the sample file" (not the function with fx) 
and edit the M code via Advanced Editor.

Change the inside of {...} :

Source{[Item="Extract01",Kind="Sheet"]}[Data]

Into : 

Source{0}[Data]

 

It should do the trick, let us know...

 

@AilleryO 

 

in with one?

Frixel_0-1641402064987.png

 

If both queries have a reference to your sheets name, you need to change in both queries.

 

Otherwise just in the one, with sheets name 😉

 

The line with sheets name is always on top, should be the step called Navigate.

 

Let us know...

@AilleryO 

 

I loaded the data again and i have now one Transform sample file.

I put the code 

= Source{0}[Data]

into the place below in the navigation and this error happens

 

Frixel_1-1641403473136.png

 

 

 

 

First, you need to select the query on the left, exactly as you did.

Then select the step Navigate on the right (Applied steps)

You should see in the formula bar (your picture) the old formula with the sheet name.

Once you've located this line you do the replacement in formula bar, in fact you need to modify the existing line, not to create a new one.

Tell us if it works...

ChangeSheetName.pngWith a picture might be easier 😉

and after it should be like this :

ChangeSheetName_after.png

Do not forget that the most left is 0, then 1. So you might have to change the number if your data are not on the most left sheet of your files.

 

You might have to add a step to promote headers but not always the case. Just check if your headers are in place otherwise add a step after Navigate to promote headers.

 

Tell us if it works...

parry2k
Super User
Super User

@Frixel I know what the fix is but hard to explain, can you send pbix file with two sample files one with table and one without table and I will put together the solution.

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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