rank in powerquery Column and function to concatenate each row Rank with an other ColumnsData

[PhilippeMuniesa]

Hello,

To continue the project for which Jimmy801  (Re: TCD from Database) gave me an answer, I notice that I have duplicates in the database, and that these duplicates should not be deleted. prior to the Pivot solution I must therefore ensure that there are no more duplicates, to do this I must to get the rank of each identical occurrence (Duplicate) in a column, and concatenate it with the content of another columnn 

starting point:

Ident	Rubrique
2630775108002'	S21.G00.50.008
2630775108002'	S21.G00.50.009
2630775108002'	S21.G00.51.001
2630775108002'	S21.G00.51.001
2630775108002'	S21.G00.51.001
2630775108002'	S21.G00.51.001
2630775108002'	S21.G00.51.002
2630775108002'	S21.G00.51.002
2630775108002'	S21.G00.51.002
2630775108002'	S21.G00.51.002
2630775108002'	S21.G00.53.001
2630775108002'	S21.G00.53.001
2630775108002'	S21.G00.53.002
2630775108002'	S21.G00.53.002

desired outcome

Ident	Rubrique	Rank	ident&rank
2630775108002'	S21.G00.50.008	1	2630775108002'_1
2630775108002'	S21.G00.50.009	1	2630775108002'_1
2630775108002'	S21.G00.51.001	1	2630775108002'_1
2630775108002'	S21.G00.51.001	2	2630775108002'_2
2630775108002'	S21.G00.51.001	3	2630775108002'_3
2630775108002'	S21.G00.51.001	4	2630775108002'_4
2630775108002'	S21.G00.51.002	1	2630775108002'_1
2630775108002'	S21.G00.51.002	2	2630775108002'_2
2630775108002'	S21.G00.51.002	3	2630775108002'_3
2630775108002'	S21.G00.51.002	4	2630775108002'_4
2630775108002'	S21.G00.53.001	1	2630775108002'_1
2630775108002'	S21.G00.53.001	2	2630775108002'_2
2630775108002'	S21.G00.53.002	1	2630775108002'_1
2630775108002'	S21.G00.53.002	2	2630775108002'_2

thank you for help from members or super users

thank you in advance

Philippe Muniesa

[CNENFRNL · Accepted solution]

@PhilippeMuniesa , it's necessary to index the table in Power Query to differentiate identical rubriques within a ident group; then you can use such a calculated column,

Rank =
DS[Rubrique] & "_"
    & RANKX (
        FILTER ( DS, DS[Rubrique] = EARLIER ( DS[Rubrique] ) ),
        DS[Index],
        ,
        ASC
    )

Or you might want to try a solution in Power Query,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIzNjA3NzU0sDAwMIopBRLmSjpKwUaGeu4GBnqmBnoGBhZKsTpEKbQkRqEhUKHhEFBoNMgVGhPrGZIUEm01UGEsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ident = _t, Rubrique = _t]),
    #"Grouped Rows" = Table.RemoveColumns(Table.Group(Source, {"Ident", "Rubrique"}, {{"ar", each Table.AddIndexColumn(_, "Index", 1)}}), {"Ident","Rubrique"}),
    #"Expanded ar" = Table.ExpandTableColumn(#"Grouped Rows", "ar", {"Ident", "Rubrique", "Index"}, {"Ident", "Rubrique", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded ar", "ident&rank", each [Rubrique] & "_" & Text.From([Index]))
in
    #"Added Custom"

[Anonymous]

You'll need to add a basic index column for this to work, which can be added in Power Query> Add columns>index column. 

Be sure to change 'Table' with the name of the table you're using. 

ident&rank =
VAR _Occurrences =
    CALCULATE (
        COUNT ( [Rubrique] ),
        FILTER (
            'Table',
            [Index] <= EARLIER ( 'Table'[Index] )
                && [Rubrique] = EARLIER ( 'Table'[Rubrique] )
        )
    )
RETURN
    'Table'[Ident] & "_" & _Occurrences