Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
KatBous
Frequent Visitor

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?

2 REPLIES 2
AntrikshSharma
Community Champion
Community Champion

@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

AntrikshSharma_0-1669724018680.png

 

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? 

 

KatBous_1-1669731277129.png

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors