Combine all Worksheets in a Workbook with Power Query

[@wayneedmondson1065]

Awesome Ken! Thanks for walking through the steps. Well explained. Easy to follow. Thumbs up!!

[@andrewsinha2785]

Hi Ken, Thanks you for this very useful transformation. More than that I appreciate the content time stamps and the calm smooth delivery. Thanks again Andrew

[@skillwavetraining]

Glad it was helpful!

[@zoranmilokanovic6229]

Very useful video. If I may suggest, try replacing the hard-coded columns in the Expanded Transform Sheet step with the Table.ColumnNames(#"Sample Sheet Transform"). This is how it works with the one-click transform PQ solution that combines all files from a folder.

[@skillwavetraining]

True enough although... while that will dynamically pull the headers from the first nested table, it won't get additional columns that appear (only) in subsequent columns. I'm not saying it isn't a good practice, you're correct that I should have done this, but I think that - if I'm concerned about dynamic column names - I'd go the extra mile and build something to look at expanding all columns from each table. ;)

[@zoranmilokanovic6229]

I was guided by the functionality of From File/Folder, which comes out-of-the-box. There, the header is taken dynamically from the Transform Sample File-hence the suggestion. Especially since, in your example, all the sheets have the same header. I completely agree with you that if this weren't the case, a more robust solution would be necessary. In the Output query, the existing step for expanding would be replaced with the following two steps Header = List.Union(List.Transform(#"Removed Other Columns"[Transform Sheet], Table.ColumnNames)), #"Expanded Transform Sheet" = Table.ExpandTableColumn(#"Removed Other Columns", "Transform Sheet", Header),

[@westleyempeigne6541]

Hi Ken, great solution. Will that work in cases where some of the sheet may have more columns or less columns? Or if the name of these columns in one sheet may be a little bit different to the other sheets?

[@KenPulsXLGuru]

Hi Westley, as always, it depends. If there are more/less columns, providing we don't do anything that specifically refers to a column name in the code that may not be there in future, we should be fine. With regards to the comment about "different", it really depends on "how different". If it is similar, but needs a slightly different treatment, you may be able to code that into your function. If it is totally different (say sales data vs production data which has a TOTALLY different makeup), then you'll need to create a second function and filter the files list allowing you to call the correct function for the correct files.