Consolidating multiple files | different sheet names

[Easyluke]

Hi,

I'm trying to consolidate different excel spreadsheet in a folder that have minor different needed sheet names. I've tried to use parameters from transform file - Sample files but not very successfully. Does anyone know how to achiveve this?

= (Parameter1) => let
Source = Excel.Workbook(Parameter1, null, true),
#"Project Tracker_Sheet" = Source{[Item="Project Tracker",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Project Tracker_Sheet", [PromoteAllScalars=true])
in
#"Promoted Headers"

What I'd neet to achieve is something similar to using a wildcard

#"Project Tracker_Sheet" = Source{[Item="Project Tracker",Kind="Sheet"]}[Data],

Project Tracker* - to import for example all Sheets name Project Tracker and Project Tracker + something else:

Project Tracker

Project Tracker - A

Project Tracker - B...

Thank you for your help.

Lk

[v-henryk-mstf · Accepted solution]

Hi @Easyluke ,

To import all sheets with names that begin with "Project Tracker" using the M language in Power Query, you can use the following code:

= (Parameter1) => let
Source = Excel.Workbook(Parameter1, null, true),
#"Matched Sheets" = Source{[Name Starts With "Project Tracker",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Matched Sheets", [PromoteAllScalars=true])
in
#"Promoted Headers"

This code will first retrieve all sheets in the workbook that have names that start with "Project Tracker", and then it will promote the headers of all those sheets. Finally, it will return a list of all the promoted sheets.

You can also use the List.Transform function to apply the same transformation to each sheet in the list, and then use the Table.Combine function to combine all the resulting tables into a single table. Here's an example of how you could do that:

= (Parameter1) => let
Source = Excel.Workbook(Parameter1, null, true),
#"Matched Sheets" = Source{[Name Starts With "Project Tracker",Kind="Sheet"]}[Data],
#"Promoted Sheets" = List.Transform(#"Matched Sheets", each Table.PromoteHeaders(_, [PromoteAllScalars=true])),
#"Combined Table" = Table.Combine(#"Promoted Sheets")
in
#"Combined Table"

This will apply the Table.PromoteHeaders transformation to each sheet in the list of matched sheets, and then combine all the resulting tables into a single table.

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[Anonymous]

Hi Henry.

I have just tried to use your Solution for the wildcard sheet names method.

When I copy and paste in to my Advanced Editor, it states 'Invalid idenitifier' re: 'Name'.

Can you help me please?

Thank you

Ian