Forum Discussion
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
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]))
8 Replies
Anonymous , what you want to display. You can use sum/min/max
Anonymous , You need to take Min/max of the column to have a nonunique value that can be based on date.
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:
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
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:
Wish it is helpful for you!
Click here to download pbix if you need.
Best Regard
Lucien Wang
