Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
PowerBINoob12
Helper I
Helper I

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

 

PowerBINoob12_0-1669980196100.png


Appreicate any help with this.

 

Thanks.

1 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

View solution in original post

4 REPLIES 4
mangaus1111
Solution Sage
Solution Sage

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.

 

mangaus1111_0-1669980964551.png

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

 

 

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

@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

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.