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

[Sonam_Pillai]

I have a data like this in power query,

CALL NO.	Sheet1.Count (OUTPUT AFTER GROUOPING AND INDEXING
ABC	1
PQR	1
ERT	1
DEF	1
ASD	2
ASD	2
FGH	1
TGY	1
POL	2
POL	2

However i need out put like below

CALL NO.	Sheet1.Count (OUTPUT AFTER GROUOPING AND INDEXING
ABC	1
PQR	1
ERT	1
DEF	1
ASD	0
ASD	1
FGH	1
TGY	1
POL	0
POL	1

[Greg_Deckler]

Calling in the calvary...@ImkeF

[ImkeF · 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 😉 )

[Dylan73]

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

[alexanderc]

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

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

[alexanderc]

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!

[jp_powerbiuser]

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

Many thanks 🙂

[azanzini]

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

[Sonam_Pillai]

Hey ImkeF

Wow..its worked like wonder for me..

Thanks a ton for this 🙂

You are a STAR ***