Forum Discussion

rfernandez's avatar
rfernandez
Regular Visitor
7 years ago
Solved

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...
Need Help
  • HotChilli's avatar
    HotChilli
    7 years ago

    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"

HotChilli's avatar
HotChilli
Community Champion
7 years ago

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's avatar
    rfernandez
    Regular Visitor
    7 years ago

    that worked perfectly thanks :)