Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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
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.
Hi @cgkas ,
Please reference the M query below.
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
Hi, someone have and idea how to do it?
Thanks
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
73 | |
65 |