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 code Start Date End Date a_001 01/01/2022...

Need Help

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