cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rfernandez Frequent Visitor
Frequent Visitor

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.

namezonecode
ASouthX
ASouthY
ASouthZ
ASouthXX
BWestX
BWest 
BWestYY
CSouthX
CSouthXX
CSouth 
CSouthZZ

FIRST TABLE

 

 

namezoneXYZXXYYZZ
ASouth1     
ASouth 1    
ASouth  1   
ASouth   1  
BWest1     
BWest      
BWest    1 
CSouth1     
CSouth   1  
CSouth      
CSouth     1

SECOND TABLE

 

 

 

namezoneXYZXXYYZZ
ASouth1111  
BWest1   1 
CSouth1  1 1

THIRD TABLE

 

1 ACCEPTED SOLUTION

Accepted Solutions
HotChilli Senior Member
Senior Member

Re: Transform table with duplicate values

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 3
HotChilli Senior Member
Senior Member

Re: Transform table with duplicate values

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 Frequent Visitor
Frequent Visitor

Re: Transform table with duplicate values

that worked perfectly thanks Smiley Happy

Highlighted
Super User
Super User

Re: Transform table with duplicate values

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"