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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

@Anonymous , 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 @Anonymous ,

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
Community Champion
Community Champion

Hi @Anonymous ,

 

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 @Anonymous ,

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

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

Anonymous
Not applicable

Hi @amitchandak 

 

I need to remove any one line.

 

Thanks,

KV's

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

Anonymous
Not applicable

Dim date.PNG

 

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

@Anonymous , 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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.