Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello
I need to transform a table where there are duplicate values, the idea is to move from the first table to the third table, where the first and second columns do not have duplicate values and last column becomes a row.
Thank you.
name | zone | code |
A | South | X |
A | South | Y |
A | South | Z |
A | South | XX |
B | West | X |
B | West | |
B | West | YY |
C | South | X |
C | South | XX |
C | South | |
C | South | ZZ |
FIRST TABLE
name | zone | X | Y | Z | XX | YY | ZZ |
A | South | 1 | |||||
A | South | 1 | |||||
A | South | 1 | |||||
A | South | 1 | |||||
B | West | 1 | |||||
B | West | ||||||
B | West | 1 | |||||
C | South | 1 | |||||
C | South | 1 | |||||
C | South | ||||||
C | South | 1 |
SECOND TABLE
name | zone | X | Y | Z | XX | YY | ZZ |
A | South | 1 | 1 | 1 | 1 | ||
B | West | 1 | 1 | ||||
C | South | 1 | 1 | 1 |
THIRD TABLE
Solved! Go to Solution.
Duplicate the 'zone' column then pivot on the 'code' column using 'zone-Copy' for values. You'll have to remove one of the columns after that to tidy up your results.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQrOLy3JANIRSrE6qCKRGCJRGCIREG1OQGZ4anEJ3BwkAQV0gUiIwc4YljtjGowspIAhEgV0UCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, zone = _t, code = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"zone", type text}, {"code", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "zone", "zone - Copy"),
#"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[code]), "code", "zone - Copy", List.Count),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{" "})
in
#"Removed Columns"
Hi,
This M code works as well
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"zone", type text}, {"code", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([code] <> null)),
#"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows", "code", "code - Copy"),
#"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[code]), "code", "code - Copy", List.Count)
in
#"Pivoted Column"
Duplicate the 'zone' column then pivot on the 'code' column using 'zone-Copy' for values. You'll have to remove one of the columns after that to tidy up your results.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQrOLy3JANIRSrE6qCKRGCJRGCIREG1OQGZ4anEJ3BwkAQV0gUiIwc4YljtjGowspIAhEgV0UCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, zone = _t, code = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"zone", type text}, {"code", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "zone", "zone - Copy"),
#"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[code]), "code", "zone - Copy", List.Count),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{" "})
in
#"Removed Columns"
that worked perfectly thanks 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |