Counting and removing duplicates from a table in PowerBI

[PowerBINoob12]

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 creating a new column that counts how many times the toy appears and then removes rows so were left with a tidy table.

Appreicate any help with this.

Thanks.

[mangaus1111]

Hi @PowerBINoob12 ,

in this case you don't need to create a new column.

You just need to use a table visual with the 2 times the column [Type]: one time as category, second time as count  like the n the below example.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PowerBINoob12]

I actually need this done on the backend so that I can merge the count of toys table to another table that contains a list of all toys. Thanks though

[Manoj_Nair · Accepted solution]

@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]

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