Multiple worksheets in sharepoint hosted Excel

Hi chur06 

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"