Forum Discussion
Solved
Table.Distinct with the condition in PowerQuery
Hi,
I have a problem with PowerQuery. So, I need to apply Distinct function in my table.
But with one condition: repeatable values should be deleted if their value is greater than 0.
Please help me to write a fuction on PowerQuery M. Maybe it's possible to use IF-expression?
Example, if 'ID'>0 then Table.Distinct(#"", {"ID"}) else 0.
Thanks!
this should work
Table.FromRows(List.Distinct(Table.ToRows(Source),each if _{0}=0 then Text.NewGuid() else _{0}))
3 Replies
kmatveyko , I think you need first filter the values and then take distinct
Not quite. For example, here is a column:
0 0 20155 20156
20157 20157 20157 0 20158 20159 Needs to delete duplicated values 20157 (two times). Nulls should stay in a column as they are.
Here is a required result:
0 0 20155 20156 20157 0 20158 20159 this should work
Table.FromRows(List.Distinct(Table.ToRows(Source),each if _{0}=0 then Text.NewGuid() else _{0}))
