Merge Multiple tables from Multiple Sheets

Power Query
1
  1. My excel workbook has week wise site report of quantities.
  2. In each table, first 4 columns are same. The remaining columns are different based on category.
  3. I want merge all tables keeping first 4 columns same and unpivoting remaining columns.
  4. There is possibility of additional tables in upcoming weeks. But first 4 columns will be same.
  5. The expected output is in “Goal” Sheet.
    Merge Multiple Tables from Multiple Sheets.xlsx (24.0 KB)
2

Hi Prafulla,

this works great for the given data:
-added columns are covered
-first column W gives you the sheet name

let
    Source = Excel.Workbook(File.Contents("C:\ .. YourPath ..\Merge Multiple Tables from Multiple Sheets.xlsx"), null, true),
    #"Filtered W" = Table.SelectRows(Source, each Text.StartsWith([Item], "W-"))[[Name],[Data]],
    #"Expanded Data" = Table.ExpandTableColumn(#"Filtered W", "Data", Table.ColumnNames(Table.Combine(#"Filtered W"[Data]))),
    #"Filtered Empty Rows" = Table.SelectRows(#"Expanded Data", each ([Column1] <> null)),
    Split = Table.Split(#"Filtered Empty Rows",7),
    #"Converted to Table" = Table.FromList(Split, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each 
        Table.RenameColumns(
            Table.PromoteHeaders([Column1], [PromoteAllScalars=true]),
        {{[Column1][Name]{0}, "W"}}))[[Custom]],
    #"Expanded Custom" = Table.Combine(#"Added Custom"[Custom]),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom", List.Select(Table.ColumnNames(#"Expanded Custom"), each List.MatchesAny(Table.Column(#"Expanded Custom", _), each _ <> null ))),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"W", "WEEK NO", "CATEGORY", "SUB-CATEGORY", "AREA CODE"}, "Parameter", "Value")
in
    #"Unpivoted Other Columns"

Regards,
Matthias

3

Thank You for your reply.

Thank You for your help. It is working for given set of data.

In my actual data, number of rows in each table are different.

In above solution , for table.split page size is considered 7. What if want to make it variable.

4

Well, the solution answered precisely your description and your data set.
I adapted with grouping to your new description, i.e. variable number of rows per sub-table.

=> If your data is still different, you should open a new thread with a correct data set and description.:blush:

let
    Source = Excel.Workbook(File.Contents("C:\ ... \Merge Multiple Tables from Multiple Sheets.xlsx"), null, true),
    #"Filtered W" = Table.SelectRows(Source, each Text.StartsWith([Item], "W-"))[[Name],[Data]],
    #"Expanded Data" = Table.ExpandTableColumn(#"Filtered W", "Data", Table.ColumnNames(Table.Combine(#"Filtered W"[Data]))),
    #"Filtered Empty Rows" = Table.SelectRows(#"Expanded Data", each ([Column1] <> null)),
    #"Added Custom1" = Table.AddColumn(#"Filtered Empty Rows", "ID", each if [Column1]="WEEK NO" then null else Text.From([Column1])&[Column2]&[Column3]),
    #"Filled Up" = Table.FillUp(#"Added Custom1",{"ID"}),
    #"Grouped Rows" = Table.Group(#"Filled Up", {"ID"}, {{"All", each _, type table [Name=text, Column1=any, Column2=text, Column3=text, Column4=any, Column5=any, Column6=any, Column7=any, Column8=any, Column9=any, Column10=any, ID=nullable text]}})[[All]],
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each 
        Table.RenameColumns(
            Table.PromoteHeaders(
                Table.RemoveColumns([All], {"ID"}),
             [PromoteAllScalars=true]),
        {{[All][Name]{0}, "W"}}))[[Custom]],
    #"Expanded Custom" = Table.Combine(#"Added Custom"[Custom]),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom", List.Select(Table.ColumnNames(#"Expanded Custom"), each List.MatchesAny(Table.Column(#"Expanded Custom", _), each _ <> null ))),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"W", "WEEK NO", "CATEGORY", "SUB-CATEGORY", "AREA CODE"}, "Parameter", "Value")
in
    #"Unpivoted Other Columns"
5

Thank You Very Much

1 Like