Combine multiple worksheets when loading a folder

[Anonymous]

Hello everyone, I have a problem that I can't solve, I have some workbooks that I'll load from folder, each workbook has "sheet 1", "Sheet 2" with same data structure and a tab "List", and I want to combine in one table every  "sheet 1" and "sheet 2" from all of the workbooks, but when I load the folder I'm able to only select one tab for example "sheet 1". I'll apreciate any help
Thanks in advance

[Ashish_Mathur]

Hi,

You may follow the steps outlined in this video - https://www.youtube.com/watch?v=yL11ugShdrk.

[KNP · Accepted solution]

Hi @Anonymous ,

I would solve it by adding a step in the 'Transform Sample File' code to deal with the first and second sheets independently.

Transform Sample File code:

let
    Source = Excel.Workbook(Parameter1, null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    FirstSheet = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data],
    SecondSheet = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]),
    Custom1 = Table.Combine({FirstSheet, SecondSheet})

in
    Custom1

Note that 'Sheet1_Sheet' references 'Source' and 'Sheet2_Sheet' also references 'Source' (not the previous step). 

Then I just used 'Table.Combine' to join the two results together.

Here is the code from the main query:

let
    Source = Folder.Files("C:\Temp"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))
in
    #"Expanded Table Column1"

Nothing special going on here. This is the standard pattern you get when combining files.

I may have summarized this a little too much depending on your understanding of M. If you need further explanation please let me know.

Regards,

Kim

[Anonymous]

this works perfectly, thank u so much