Forum Discussion

PowerBINoob12's avatar
PowerBINoob12
Icon for Helper I rankHelper I
3 years ago
Solved

Counting and removing duplicates from a table in PowerBI

Hi All,   I have a table below with a list of toys and I'd like to change column A to look like Column D and Column E. Essentially counting how many times each item appears in column A and then ...
Need Help
  • Manoj_Nair's avatar
    Manoj_Nair
    3 years ago

    PowerBINoob12 - try this transformation step. 

    PBIX FILE or else copy the advanced editor -

     

    let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSk4sUorVwUYXl+SkojNT8nNyUBk5qen5OBgl+ZVKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t]),

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}}),

#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Type", "Type - Copy"),

#"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[#"Type - Copy"]), "Type - Copy", "Type", List.Count),

#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Pivoted Column", {}, "Attribute", "Value")

in

#"Unpivoted Columns"
-

     

    it should work.

     

    If this post help, pls consider Accept it as the solution. Thanks

Manoj_Nair's avatar
Manoj_Nair
Icon for Solution Supplier rankSolution Supplier
3 years ago

PowerBINoob12 - try this transformation step. 

PBIX FILE or else copy the advanced editor -

 

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSk4sUorVwUYXl+SkojNT8nNyUBk5qen5OBgl+ZVKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t]),

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}}),

#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Type", "Type - Copy"),

#"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[#"Type - Copy"]), "Type - Copy", "Type", List.Count),

#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Pivoted Column", {}, "Attribute", "Value")

in

#"Unpivoted Columns"
-

 

it should work.

 

If this post help, pls consider Accept it as the solution. Thanks

PowerBINoob12's avatar
PowerBINoob12
Icon for Helper I rankHelper I
3 years ago

Thank you so much - attaching the PBIX file really really helped.