Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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]))
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
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
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.
@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.
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]))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
96 | |
79 | |
66 | |
62 |
User | Count |
---|---|
145 | |
113 | |
105 | |
85 | |
65 |