Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Experts!
I have many tables in Excel under each other and I would like to merge it to one dataset. Can´t figure out how Power Querry can understand that new table start with each time when row include SIZE.
For example:
SIZE | 7 | 8 | 9 |
Red | 3 |
| 3 |
Blue |
|
| 6 |
|
|
|
|
SIZE | 4 | 5 | 6 |
Red |
|
| 4 |
Black |
|
| 4 |
|
|
|
|
SIZE | 11 | 12 | 13 |
Green |
|
| 15 |
Gray | 15 |
|
|
|
|
|
|
SIZE | 4 | 5 | 6 |
White |
| 2 |
|
Black | 3 |
|
|
And I would like to see it like this:
COLOR | SIZE | QTY |
Red | 7 | 3 |
Red | 9 | 3 |
Blue | 9 | 6 |
Red | 6 | 4 |
Black | 6 | 4 |
Green | 13 | 15 |
Gray | 11 | 15 |
White | 5 | 2 |
Black | 4 | 3 |
Thanks to all in advance!
Hi @Anonymous ,
First in the 4 original tables,do the following transformations:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvaMclXSUTIHYgsgtlSK1YlWCkpNAbKNgRhMgYScckpTIXwgMlOKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"SIZE", Int64.Type}, {"Red", Int64.Type}, {"Blue", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"SIZE"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Color"}, {"Value", "Qty"}})
in
#"Renamed Columns"
And you will see each table is transformed to below format:
Then append tables ,and you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
let
Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkpNUdJRMgZiMBWrE63klFOaCuEDkZlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SIZE = _t, #"7" = _t, #"8" = _t, #"9" = _t]),
Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkpNUdJRgiATpVidaCWnnMTkbGSxWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SIZE = _t, #"4" = _t, #"5" = _t, #"6" = _t]),
Table3 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci9KTc1T0lGCIENTpVgdkGBiJYQHkYiNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SIZE = _t, #"11" = _t, #"12" = _t, #"13" = _t]),
Table4 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs/ILElV0lECIiMQFasTreSUk5icDeQYQ8SVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SIZE = _t, #"4" = _t, #"5" = _t, #"6" = _t]),
#"Merged Tables" = Table1 & Table2 & Table3 & Table4,
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Merged Tables", {"SIZE"}, "Size", "Qty"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Qty] <> "")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"SIZE", "Color"}})
in
#"Renamed Columns"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |