cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Udt_KV
Post Partisan
Post Partisan

Row Duplicate

Hi Team,

 

I have table (final Table) and got duplicate in few columns,

 

Capture1.PNG

Dim date.PNG

 

above highlighted data are same but next column value is deffer so I can't remove it.

 

can you share any DAX/Edit query to remove this problem.

 

Thanks,

KV's

1 ACCEPTED SOLUTION

@Udt_KV , Add an index column and use these two measures in place of 2D and WIP-OI

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

 

firstnonblankvalue(table[index], max(Table[2D]))

 

 

Lastnonblankvalue(table[index], max(Table[WIP-OI]))

View solution in original post

8 REPLIES 8
v-luwang-msft
Community Support
Community Support

Hi @Udt_KV ,

You could use  the following  measure:

WipOI =

VAR test1 =

    CALCULATE (

        MAX ( 'Table'[WIP-OI] ),

        FILTER ( ALL ( 'Table' ), 'Table'[2D] = MAX ( 'Table'[2D] ) )

    )

VAR test2 =

    MAX ( 'Table'[WIP-OI] )

VAR test3 =

    IF ( test1 = test2, test1, BLANK () )

VAR test4 =

    CALCULATE (

        MAX ( 'Table'[2D] ),

        FILTER ( ALL ( 'Table' ), 'Table'[WIP-OI] = MAX ( 'Table'[WIP-OI] ) )

    )

VAR test5 =

    MAX ( 'Table'[2D] )

VAR test6 =

    IF ( test1 = test2 && test5 = test4, test1, BLANK () )

RETURN

    test6

 

 

Final you will see the below:

v-luwang-msft_0-1614075463814.png

 

Wish  it is helpful for you!

 

Click  here  to download pbix if you need.

 

Best Regard

Lucien Wang

PC2790
Super User
Super User

Hi @Udt_KV ,

 

You can do a group by in Power query based on the columns that you want to base your deduplication.

Something like this:

 

PC2790_0-1613971401967.png

Then create a custom column to get the first row from the Newtable, code below:

= Table.FirstN([NewTable],1)

 

PC2790_1-1613971509819.png

I hope this works for you

nowreena21
Frequent Visitor

Hello @Udt_KV ,

You can also try to select the columns which have same value (in your case first 3), then hit remove duplicate.

Make sure the column selection when grouped together always have unique value.

amitchandak
Super User
Super User

@Udt_KV , what you want to display. You can use sum/min/max

Hi @amitchandak 

 

I need to remove any one line.

 

Thanks,

KV's

@Udt_KV , You need to take Min/max of the column to have a nonunique value that can be based on date.

Dim date.PNG

 

see above table I have highlighted columns different from other columns, So i need to remove any one.

@Udt_KV , Add an index column and use these two measures in place of 2D and WIP-OI

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

 

firstnonblankvalue(table[index], max(Table[2D]))

 

 

Lastnonblankvalue(table[index], max(Table[WIP-OI]))

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.