cancel
Showing results for
Did you mean:
Post Partisan

## Row Duplicate

Hi Team,

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

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
Super User

@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]))

8 REPLIES 8
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:

Wish  it is helpful for you!

Click  here  to download pbix if you need.

Best Regard

Lucien Wang

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:

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

``= Table.FirstN([NewTable],1)``

I hope this works for you

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.

Super User

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

Post Partisan

I need to remove any one line.

Thanks,

KV's

Super User

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

Post Partisan

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

Super User

@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]))

Announcements

#### 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.

#### 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!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors