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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
II1154
Frequent Visitor

count the number of duplicates

Bonjour je cherche un moyen avec List de trouver le nombre de doublons (nb.if) sans passer par le doublon du tableau sur powerquery, merci d’avance

occurrencenb.SI
X12
X24
X33
X41
X51
X62
X24
X33
X91
X101
X12
X121
X24
X33
X62
X24
1 ACCEPTED SOLUTION
ronrsnfld
Community Champion
Community Champion

  • Add an Index Column
  • Group by occurrence
    • Aggregate by "All Rows"
    • Aggregate by "Count"
  • Expand the "All Rows"
  • Sort back to original order using the Index column

Another method that may be less efficient is to add a custom column with the formula:

  List.Count(List.Select(#"Changed Type"[occurrence], (li)=> li = [occurrence]))

 

To duplicate, paste the code below into a blank query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WijBUitUBUkYQyhhCmUAoUwhlhk2JJYQyNIDSUMoIm1qEAbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [occurrence = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"occurrence", type text}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Count", each 
        List.Count(
            List.Select(#"Changed Type"[occurrence], 
                (li)=> li = [occurrence])), Int64.Type)
in
    #"Added Custom"

ronrsnfld_0-1678069091224.png

 

 

 

View solution in original post

3 REPLIES 3
ronrsnfld
Community Champion
Community Champion

  • Add an Index Column
  • Group by occurrence
    • Aggregate by "All Rows"
    • Aggregate by "Count"
  • Expand the "All Rows"
  • Sort back to original order using the Index column

Another method that may be less efficient is to add a custom column with the formula:

  List.Count(List.Select(#"Changed Type"[occurrence], (li)=> li = [occurrence]))

 

To duplicate, paste the code below into a blank query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WijBUitUBUkYQyhhCmUAoUwhlhk2JJYQyNIDSUMoIm1qEAbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [occurrence = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"occurrence", type text}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Count", each 
        List.Count(
            List.Select(#"Changed Type"[occurrence], 
                (li)=> li = [occurrence])), Int64.Type)
in
    #"Added Custom"

ronrsnfld_0-1678069091224.png

 

 

 

II1154
Frequent Visitor

too bad that with a line in (list.... power query we can't have that, in powerbi in my case it can't work

Migasuke
Super User
Super User

Hi @II1154 ,

This is not possible.
You could duplocicate the table / list and apply Group By. Then merge the original table with group by table to see number of duplicates.
If you can, try solution in DAX instead.

Ce n'est pas possible. Vous pouvez dupliquer le tableau/la liste et appliquer Grouper par. Fusionnez ensuite la table d'origine avec groupe par table pour voir le nombre de doublons. Si vous le pouvez, essayez plutôt la solution dans DAX.

 



If my answer was helpful please give me a Kudos or even accept as a Solution.

Let's connect on LinkedIn!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors