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"

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

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"