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

[cgkas]

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 columns in one sheet, for exmaple if there are values in A:B, C: D and E:F I want the data in those pairs of columns in A:B and in 3rd column the sheet name. 

Is possible to do this with M language?

I'm using Excel 2016 but could be applied on Power Query within Excel.  

I import the workbook but and select all sheets, but I don't know how to select even number of columns and then merge the columns.

My data is like below (I've attached a sample workbook too here sample.xlsx)

Sheet "2434 XYZ" --> Has 9 columns but data useful is in columns that go in pairs, A:B, C: D, E:F. For E:F only first2 rows are useful since 3rd row is not in pair value, I mean, E3 doesn't have an F3 value.

2434 XYZ	2434 XYZ	2434 XYZ	2434 XYZ	2434 XYZ	2434 XYZ	2434 XYZ	2434 XYZ	2434 XYZ
1	3192	4	5301	7	1717
2	4013	5	1467	8	4112
3	4894	6	5271	9

Sheet "7735" --> Has 8 columns but data useful is again columns that go in pairs, A:B, C: D

7735	7735	7735	7735	7735	7735	7735	7735
1	3773	7	1292
2	1942	8	4769
3	1611		8668
4	4153
5	7790
6	5442

Sheet "8873" --> Has 8 columns and data useful is A:B, C: D

8873	8873	8873	8873	8873	8873	8873
1	1122	4	7101
2	5648	5	2213
3	9093	6	1112

Output desired is like this in a single sheet(table):

1	3192	2434 XYZ
2	4013	2434 XYZ
3	4894	2434 XYZ
4	5301	2434 XYZ
5	1467	2434 XYZ
6	5271	2434 XYZ
7	1717	2434 XYZ
8	4112	2434 XYZ
1	3773	7735
2	1942	7735
3	1611	7735
4	4153	7735
5	7790	7735
6	5442	7735
7	1292	7735
8	4769	7735
1	1122	8873
2	5648	8873
3	9093	8873
4	7101	8873
5	2213	8873
6	1112	8873

Thanks in advance for any help.

[v-piga-msft · Accepted solution]

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]

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]

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

[cgkas]

Hi, someone have and idea how to do it?

Thanks