How to transpose multiple xlsx files (with multiple tabs in each files), and combine in Excel Power Query?

Question

I have close to 100+ excel files each with 6 tabs or more. The format of each tabs is identical. I need to combine one of the table ("Tab required") from all the files using Power Query, but I need to transpose each table prior to combining.

Here's what I have tried (the last "Invert" line is modified from How to transpose multiple .csv files and combine in Excel power query?):

let
    Source = Folder.Files("Directory"),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content],true)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom", "Name"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Item] = "Tab Required")),
    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"Data", "Name"}),
    #"Invert" = Table.TransformColumns(#"Removed Other Columns1", {{"Data", each Table.Transpose(xlsx.Document(_))}}),
MaxColumns = List.Max(List.Transform(#"Invert"[Data], each Table.ColumnCount(_))),
    #"Expanded Content" = Table.ExpandTableColumn(#"Invert", "Data", List.Transform({1..MaxColumns}, each "Column" & Number.ToText(_)))

in
    #"Expanded Content"

But I faced below error message:

Expression error: The name 'xlsx.Document' wasn't recognized. Make sure it's spelled correctly.

Can anybody please help me on this?

horseyride horseyride · Accepted Answer · 2019-09-20T11:20:57

#"Invert" = Table.TransformColumns(#"Removed Other Columns1", {{"Data", each Table.Transpose(_)}})

or if you want column headers

#"Invert" = Table.TransformColumns(#"Removed Other Columns1", {{"Data", each Table.Transpose(Table.DemoteHeaders(_))}}),

How to transpose multiple xlsx files (with multiple tabs in each files), and combine in Excel Power Query?

1 Answers