Forum Discussion
Transform table with duplicate values
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
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"
3 Replies
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 :)
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"
