Ignoring sheet name of file combine

[Justas4478]

Hi, I have multiple files supplied to folder and I need to combine them together.
There is no problem with data itself, but problem with sheet name.
I am getting this error:

This is the source code for Transform file(3):
= (Parameter3) => let
Source = Excel.Workbook(Parameter3, null, true),
#"Sickness Period 2824_Sheet" = Source{[Item="Sickness Period 2824",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Sickness Period 2824_Sheet", [PromoteAllScalars=true])
in
#"Promoted Headers"

As you see it takes sheet name fomr excel file and when name on other excel file does not match the one in code it throws error.
Is there a way to make it that names of sheets would be ignored?

[rajendraongole1]

Hi @Justas4478 - you can update at transform function as parameter as below

= (Parameter3) => let
// Dynamically get the first sheet (ignoring sheet names)
FirstSheet = Source{0}[Data], // Index 0 refers to the first sheet

Selects the first sheet in the workbook by its index, which is 0 (Power Query uses 0-based indexing). This allows it to handle cases where the sheet names differ across the files.

Hope this works

[Justas4478]

@rajendraongole1 
Your solution partialy works, it does not throw error any more and thats how it looks.
= (Parameter3) => let
Source = Excel.Workbook(Parameter3, null, true),
// Dynamically get the first sheet (ignoring sheet names)
FirstSheet = Source{0}[Data] // Index 0 refers to the first sheet
in
FirstSheet

But it as well does not combine files correctly.
It leaves them in this state where they are just appended togethet with headers as rows.

This is query for table:

[Anonymous]

Thanks for the reply from rajendraongole1 , please allow me to provide another insight:

Hi, @Justas4478 

Regarding your question, I recommend that you first perform the Promoted Headers operation on the Transform Sample File. This query executes all the necessary extraction steps for a single file.

Additionally, it may be helpful to select the option to Skip files with errors when merging files. This will allow you to exclude any files that cause errors from the final output, ensuring a smoother process.

For further details, please refer to:

Combine files overview - Power Query | Microsoft Learn

Power Query Folder connector - Power Query | Microsoft Learn

Of course, if you have any new ideas, you are welcome to contact us.
 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Justas4478]

@Anonymous I manage to fix it, I just needed to add this code to my Transform File function.
#"Promoted Headers" = Table.PromoteHeaders(Source{0}[Data], [PromoteAllScalars=true])

[Justas4478 · Accepted solution]

This is the final version that fixed it for me.
= (Parameter3) => let
Source = Excel.Workbook(Parameter3, null, true),
// Dynamically get the first sheet (ignoring sheet names)
FirstSheet = Source{0}[Data], // Index 0 refers to the first sheet
#"Promoted Headers" = Table.PromoteHeaders(Source{0}[Data], [PromoteAllScalars=true])
in
#"Promoted Headers"

[Justas4478]

@Anonymous Thank you I will try this and let you know if it works.