Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Solved! Go to 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"
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
Proud to be a Super User! | |
@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:
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.
@v-linyulu-msft 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])
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"
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
83 | |
65 | |
49 |
User | Count |
---|---|
127 | |
108 | |
89 | |
70 | |
66 |