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

9 REPLIES 9
Greg_Deckler
Super User
Super User

Calling in the calvary...@ImkeF


@ 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!:
Mastering Power BI 2nd Edition

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?

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
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.