cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted

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

6 REPLIES 6
Highlighted
Super User IV
Super User IV

Calling in the calvary...@ImkeF


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

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

Highlighted

Hey 

 

Wow..its worked like wonder for me..

 

Thanks a ton for this 🙂

 

You are a STAR ***

Highlighted

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

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

Highlighted

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

 

Many thanks 🙂

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors