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

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
Sonam_Pillai
Helper I
Helper I

how to find duplicate numbers in power query and assign them numbers

I have a data like this in power query,

CALL NO.Sheet1.Count (OUTPUT AFTER GROUOPING AND INDEXING
ABC1
PQR1
ERT1
DEF1
ASD2
ASD2
FGH1
TGY1
POL2
POL2

 

 

However i need out put like below

 

CALL NO.Sheet1.Count (OUTPUT AFTER GROUOPING AND INDEXING
ABC1
PQR1
ERT1
DEF1
ASD0
ASD1
FGH1
TGY1
POL0
POL1
1 ACCEPTED SOLUTION

This is a solution in codeform:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMlSK1YlWCggMgrNdg0LgbBdXNzjbMdgFyDbCYLu5e8DVhLhHIsz094GrgbNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"CALL NO." = _t, Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CALL NO.", type text}, {"Count", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"CALL NO."}, {{"Count", each Table.RowCount(_), type number}, {"Partition", each Table.AddIndexColumn(_, "CountMultiple",0,1) , type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(#"Grouped Rows", "Partition", {"CountMultiple"}, {"CountMultiple"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Partition", "Result", each if [Count] > 1 then [CountMultiple] else [Count]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count", "CountMultiple"})
in
    #"Removed Columns"

But I've also recorded a little screencast where you can see how this is done via the UI (and how to use the code above if that's new to you too 😉 )

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

10 REPLIES 10
ianboothman
Frequent Visitor

hi

this is brill, BUT i have some sales orders that are in the table 7 times. See below. 

When i do the final step 

Result, I do not get the same response as you

= Table.AddColumn(#"Expanded partition", "Result", each if[Count]>1 then [CountMultiple] else [Count])

 

ianboothman_0-1737584773268.png

Result

ianboothman_1-1737584879554.png

 

Greg_Deckler
Super User
Super User

Calling in the calvary...@ImkeF



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

This is a solution in codeform:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMlSK1YlWCggMgrNdg0LgbBdXNzjbMdgFyDbCYLu5e8DVhLhHIsz094GrgbNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"CALL NO." = _t, Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CALL NO.", type text}, {"Count", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"CALL NO."}, {{"Count", each Table.RowCount(_), type number}, {"Partition", each Table.AddIndexColumn(_, "CountMultiple",0,1) , type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(#"Grouped Rows", "Partition", {"CountMultiple"}, {"CountMultiple"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Partition", "Result", each if [Count] > 1 then [CountMultiple] else [Count]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count", "CountMultiple"})
in
    #"Removed Columns"

But I've also recorded a little screencast where you can see how this is done via the UI (and how to use the code above if that's new to you too 😉 )

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Dylan73
Frequent Visitor

@ImkeF You're a STAR!!!!

 

You have no idea how long I've been searching for this!

 

BUT....

 

I was following your steps but was stuck as I could not find the "Countmultiple" in the checked boxes when I'm trying to expand it from "Partition"

Note that I could see "Countmultiple" column at the bottom of the screen.

@ImkeF  and @Dylan73 - I am having the exact same problem. I don't know what is going on and the tutorial is GREAT, but I don't have "Countmultiple" as an option to include - but I confirmed I do see it in the table before expanding.

 

Did you figure this out or have an idea of what I could check?

ImkeF
Community Champion
Community Champion

Hello @alexanderc , @jp_powerbiuser , @Dylan73 ,
sorry for the late response.
It just dawned to me that the reason for the behaviour you're seeing lies in a change in the PQ engine that automatically creates a hard-coded table type definition when using the all aggregation. In there, the manually created Index-column ("CountMultiple") is not included and will therefore not be displayed for expansion.
You can solve it by deleting the code that defines the table type (incl. the comma):

 

image.png

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

EXCELLENT! Thank you so much! And it was so simple - but just proves how very little I truly understand about these formulas and how amazing you are for helping. Thank you!

I am also having this issue but can't figure out why, any ideas?

 

Many thanks 🙂

hi,

after +10 hours of processing this part of the code - = Table.Group(#"Expanded Table Column1", {"Primary Email"}, {{"Count", each Table.RowCount(_), type number}, {"Partition", each _, type table}}) -

the application stopped and displayed this error message: An error occurred in the ‘Transform File from all contacts msd’ query. DataFormat.Error: External table is not in the expected format.
Details: MSD contact Feb 2019.pbix

 

Any clue?

the dataset is ~3M rows, 40 columns.

 

thanks a lot

Hey 

 

Wow..its worked like wonder for me..

 

Thanks a ton for this 🙂

 

You are a STAR ***

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

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

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

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

Top Solution Authors