Table.Distinct with the condition in PowerQuery

[kmatveyko]

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!

[amitchandak]

@kmatveyko , I think you need first filter the values and then take distinct

[kmatveyko]

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

[RenaudOne · Accepted solution]

this should work

Table.FromRows(List.Distinct(Table.ToRows(Source),each if _{0}=0 then Text.NewGuid() else _{0}))