Transpose and Append Multiple Tables in a Workbook Dynamically

[takoyaki]

Hi all,

I have multiple tables in the same workbook that are formatted with the column headers in one column and the corresponding data to the right (formatted this way for easier visibility of the user). See Tables 1 and 2 for examples. They all have the same naming convention beginning with the same prefix followed by an underscore and the table's number (for instance, Table_1, Table_2).

Does anyone know of a way to transpose the column header column (Column 1 in this example) and append all tables in the workbook that follow the specified naming convention to produce the Desired Output below? The goal is to create a Snapshot table that can review all of the individual tables' data at a glance.

 

[takoyaki]

Hi all,

I have multiple tables in the same workbook that are formatted with the column headers in one column and the corresponding data to the right (formatted this way for easier visibility of the user). See Tables 1 and 2 for examples. They all have the same naming convention beginning with the same prefix followed by an underscore and the table's number (for instance, Table_1, Table_2).

Does anyone know of a way to transpose the column header column (Column 1 in this example) and append all tables in the workbook that follow the specified naming convention to produce the Desired Output below? The goal is to create a Snapshot table that can review all of the individual tables' data at a glance.

View attachment 92062

For clarification, I am using Power Query in Excel.

 

[takoyaki]

For clarification, I am using Power Query in Excel.

Here is how I solved the problem; if anyone has any other solutions please feel free to share!

let
    Source = Excel.CurrentWorkbook(),
    FilteredRO = Table.SelectRows(Source, each Text.StartsWith([Name], "Table_")),
    ExpandedContent = Table.ExpandTableColumn(FilteredRO, "Content", {"Column1", "Column2"}, {"Column1", "Column2"}),
    PivotedColumn = Table.Pivot(Table.TransformColumnTypes(ExpandedContent, {{"Column1", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(ExpandedContent, {{"Column1", type text}}, "en-US")[Column1]), "Column1", "Column2"),
in
    PivotedColumn