topic Re: Table.Distinct with the condition in PowerQuery in DAX Commands and Tips

Table.Distinct with the condition in PowerQuery

kmatveyko — Mon, 20 Jun 2022 15:40:38 GMT

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 — Tue, 21 Jun 2022 04:12:13 GMT

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

kmatveyko — Tue, 21 Jun 2022 11:35:12 GMT

Not quite. For example, here is a column:

Needs to delete duplicated values 20157 (two times). Nulls should stay in a column as they are.

Here is a required result:

RenaudOne — Fri, 23 Sep 2022 11:48:07 GMT

this should work

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