Forum Discussion

KatBous's avatar
KatBous
Frequent Visitor
3 years ago

Identify duplicates and rank them based on another column shorting

Hi to everybody!

 

I have a table with position codes who can appear more than once as in one period can be many short term contracts.

Position codeStart DateEnd Date

a_001

01/01/202230/05/2022
a_00201/01/202230/01/2022
a_00201/02/202228/02/2022
a_00201/03/202230/04/2022
b_00101/06/202230/08/2022
b_00201/08/2022030/03/2022
b_00301/05/202230/06/2022
b_00302/07/202230/07/2022

 

I want to create in power query: a) a column e.g. duplicates based on which I can see the duplicate values, and b) for the duplicate ones to have a colum index with values for earliest to latest start date. This is how I imagine it:

 

Position codeStart DateEnd DateDuplicateDuplicate index

a_001

01/01/202230/05/2022no(1 or blank, haven't decided)
a_00201/01/202230/01/2022yes1
a_00201/03/202230/04/2022yes2
a_00201/02/202228/02/2022yes3
b_00101/06/202230/08/2022no(1 or blank, haven't decided)
b_00201/08/2022030/03/2022no1(1 or blank, haven't decided)
b_00301/05/202230/06/2022yes1
b_00302/07/202230/07/2022yes2

 

Any ideas?

Need Help

2 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    3 years ago

    KatBous Paste this code in the advanced editor:

    let
    Source = Table.FromRows (
        Json.Document (
            Binary.Decompress (
                Binary.FromText (
                    "bc7BCcAgEETRXjwLjrPR2ItIMP0XEUFN1iDs5cNj2JxNvQBvrIF37QiyhcAh9Ci2G+6M3xtOw/TG34jeOT5zq3+iNmk1cydpI6uRYYI2cWPocGozopQH",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
        in
            type table [ #"Position code" = _t, #"Start Date" = _t, #"End Date" = _t ]
    ),
    ChangedType = 
        Table.TransformColumnTypes (
            Source,
            { { "Position code", type text }, { "Start Date", type date }, { "End Date", type date } },
            "en-GB"
        ),
    GroupedRows = 
    Table.Group (
            ChangedType,
            { "Position code" },
            {
                {
                    "Transformation",
                    ( Group ) =>
                        Table.AddIndexColumn (
                            Table.AddColumn (
                                Group,
                                "Duplicate",
                                each if Table.RowCount ( Group ) > 1 then "yes" else "no"
                            ),
                            "Duplicate Index",
                            1,
                            1
                        ),
                    type table [
                        Start Date = date,
                        End Date = date,
                        Duplicate = text,
                        Duplicate Index = Int64.Type
                    ]
                }
            }
        ),
    ExpandedTransformation = 
        Table.ExpandTableColumn (
            GroupedRows,
            "Transformation",
            { "Start Date", "End Date", "Duplicate", "Duplicate Index" },
            { "Start Date", "End Date", "Duplicate", "Duplicate Index" }
        )
in
    ExpandedTransformation

     

    • KatBous's avatar
      KatBous
      Frequent Visitor
      3 years ago

      Thank you AntrikshSharma. This is exactly what I need.

       

      But, since my query has already some steps in the editor, could you help me on how to add the above commands?

       

      Or what I should replace in the above commands if I reference the existing query and apply in the new one the above commands?