Transform table with duplicate values

[rfernandez]

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

[Ashish_Mathur]

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"

[HotChilli · Accepted 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"

[rfernandez]

that worked perfectly thanks 🙂