Forum Discussion

PhilippeMuniesa's avatar
PhilippeMuniesa
Icon for Resolver I rankResolver I
5 years ago
Solved

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

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:
IdentRubrique
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
IdentRubriqueRankident&rank
2630775108002'S21.G00.50.00812630775108002'_1
2630775108002'S21.G00.50.00912630775108002'_1
2630775108002'S21.G00.51.00112630775108002'_1
2630775108002'S21.G00.51.00122630775108002'_2
2630775108002'S21.G00.51.00132630775108002'_3
2630775108002'S21.G00.51.00142630775108002'_4
2630775108002'S21.G00.51.00212630775108002'_1
2630775108002'S21.G00.51.00222630775108002'_2
2630775108002'S21.G00.51.00232630775108002'_3
2630775108002'S21.G00.51.00242630775108002'_4
2630775108002'S21.G00.53.00112630775108002'_1
2630775108002'S21.G00.53.00122630775108002'_2
2630775108002'S21.G00.53.00212630775108002'_1
2630775108002'S21.G00.53.00222630775108002'_2
thank you for help from members or super users

thank you in advance

Philippe Muniesa

 

Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    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"

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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

     

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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"