Forum Discussion

cgkas's avatar
cgkas
Icon for Helper V rankHelper V
7 years ago
Solved

Merge multiple sheets with similar structure in a single table from Excel Workbook

Hi to all,   Having data (not saved as tables) in multiple sheets in a workbook, each one with format like shown below. I want to consolidate all pairs of column of each sheet in a single pair of c...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi cgkas ,

    We could achieve your desired output in Query editor, but that should be a little complex.

    Please follow the steps. I will show the table 8873 as an example.

    1. Assuming that you load the data in Query editor like below.

    2. Remove the first row and duplicate the table

    3. Remove the columns for the two tables and then append queries.

    4. Add a custom column with the formula below.

    #"Changed Type"[Column1]{0}

    5. Please do the same modification for the other two tables and then Append the three tables.

    Here is the final output.

    For the detail steps, you could refer to the applied steps in Query Editor from my attachment.

    Best  Regards,

    Cherry

     

v-piga-msft's avatar
v-piga-msft
Icon for Resident Rockstar rankResident Rockstar
7 years ago

Hi cgkas ,

We could achieve your desired output in Query editor, but that should be a little complex.

Please follow the steps. I will show the table 8873 as an example.

1. Assuming that you load the data in Query editor like below.

2. Remove the first row and duplicate the table

3. Remove the columns for the two tables and then append queries.

4. Add a custom column with the formula below.

#"Changed Type"[Column1]{0}

5. Please do the same modification for the other two tables and then Append the three tables.

Here is the final output.

For the detail steps, you could refer to the applied steps in Query Editor from my attachment.

Best  Regards,

Cherry

 

  • cgkas's avatar
    cgkas
    Icon for Helper V rankHelper V
    7 years ago

    Hello Cherry,

     

    Thanks for answer.

     

    Would be possible for you to share the code of steps, I cannot open pbix since I'm using Excel 2016.

     

    Does your solution need to predifined each sheet content as table? because could be more than 100 sheets and they are not defined as tables.

     

    Thanks again.

    • v-piga-msft's avatar
      v-piga-msft
      Icon for Resident Rockstar rankResident Rockstar
      7 years ago

      Hi cgkas ,

      Please reference the M query below.

      Spoiler

      shared Table = let
      Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsrAwN1bSIZqK1YlWMgRyDA2NjICUCRCbGxqARBTgGKQGJGlqZmIBooDYyMjQGEMNSMTSwBJEmYGNNATpgqLYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t]),
      #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}}),
      #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
      #"Removed Other Columns" = Table.SelectColumns(#"Removed Top Rows",{"Column1", "Column2"}),
      #"Appended Query" = Table.Combine({#"Removed Other Columns", #"Table (2)"}),
      #"Added Custom" = Table.AddColumn(#"Appended Query", "Custom", each #"Changed Type"[Column1]{0}),
      #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Column1", type text}, {"Column2", type text}, {"Custom", type text}})
      in
      #"Changed Type1";

      shared Table2 = let
      Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tYyxDYAwDARXiVKnyNsmTjYBouy/Bn4KKKiRfLL1Ov+cWUwt7ceZy0/nKjMjAsWQWBZsWpl4AAdXeqB+exVKlY41Op0pIB+dnvXB5sYXcbaP4J21Lg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t]),
      #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),
      #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
      #"Removed Other Columns" = Table.SelectColumns(#"Removed Top Rows",{"Column1", "Column2"}),
      #"Appended Query" = Table.Combine({#"Removed Other Columns", #"Table2 (2)", #"Table2 (3)"}),
      #"Added Custom" = Table.AddColumn(#"Appended Query", "Custom", each #"Changed Type"[Column1]{0}),
      #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Column1", type text}, {"Column2", type text}, {"Custom", type text}}),
      #"Appended Query1" = Table.Combine({#"Changed Type1", Table1, Table})
      in
      #"Appended Query1";

      shared Table1 = let
      Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY5LCsAwCESvUlxn0UmM0bOE3P8a1baLfkKh4DA6PsTeqbVSKf21kTrBh+JTZC5ky27LTcFFCuMwdXETm3JxCAKcoYrolOM4glpeyyt0fGrrJyTeVd4/e9YYGw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t]),
      #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}}),
      #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
      #"Removed Other Columns" = Table.SelectColumns(#"Removed Top Rows",{"Column1", "Column2"}),
      #"Appended Query" = Table.Combine({#"Removed Other Columns", #"Table1 (2)"}),
      #"Added Custom" = Table.AddColumn(#"Appended Query", "Custom", each #"Changed Type"[Column1]{0}),
      #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Column1", type text}, {"Column2", type text}, {"Custom", type text}})
      in
      #"Changed Type1";

      shared #"Table1 (2)" = let
      Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY5LCsAwCESvUlxn0UmM0bOE3P8a1baLfkKh4DA6PsTeqbVSKf21kTrBh+JTZC5ky27LTcFFCuMwdXETm3JxCAKcoYrolOM4glpeyyt0fGrrJyTeVd4/e9YYGw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t]),
      #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}}),
      #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
      #"Removed Other Columns" = Table.SelectColumns(#"Removed Top Rows",{"Column3", "Column4"}),
      #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Column3", "Column1"}, {"Column4", "Column2"}}),
      #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Column1] <> null))
      in
      #"Filtered Rows";

      shared #"Table (2)" = let
      Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsrAwN1bSIZqK1YlWMgRyDA2NjICUCRCbGxqARBTgGKQGJGlqZmIBooDYyMjQGEMNSMTSwBJEmYGNNATpgqLYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t]),
      #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}}),
      #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
      #"Removed Other Columns" = Table.SelectColumns(#"Removed Top Rows",{"Column3", "Column4"}),
      #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Column3", "Column1"}, {"Column4", "Column2"}})
      in
      #"Renamed Columns";

      shared #"Table2 (2)" = let
      Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tYyxDYAwDARXiVKnyNsmTjYBouy/Bn4KKKiRfLL1Ov+cWUwt7ceZy0/nKjMjAsWQWBZsWpl4AAdXeqB+exVKlY41Op0pIB+dnvXB5sYXcbaP4J21Lg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t]),
      #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),
      #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
      #"Removed Other Columns" = Table.SelectColumns(#"Removed Top Rows",{"Column3", "Column4"}),
      #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Column3", "Column1"}, {"Column4", "Column2"}})
      in
      #"Renamed Columns";

      shared #"Table2 (3)" = let
      Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tYyxDYAwDARXiVKnyNsmTjYBouy/Bn4KKKiRfLL1Ov+cWUwt7ceZy0/nKjMjAsWQWBZsWpl4AAdXeqB+exVKlY41Op0pIB+dnvXB5sYXcbaP4J21Lg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t]),
      #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),
      #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
      #"Removed Other Columns" = Table.SelectColumns(#"Removed Top Rows",{"Column5", "Column6"}),
      #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Column6] <> "")),
      #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column5", "Column1"}, {"Column6", "Column2"}})
      in
      #"Renamed Columns";

      In addition, I have tested based on table format.

      Best  Regards,

      Cherry