Forum Discussion
Does Table.Distinct guarantee it respects order?
Hi. I see on the overview page that List.Distinct is promised to choose the *first* record in a set of equivalent records:
But there is no such comment on the Table.Distinct overview page. (And neither of these functions is documented with that behavior on their respective pages.)
I found a website that declares this is the behavior in its opening paragraph:
https://www.datalineo.com/post/power-query-sort-distinct-a-quick-tip
Is there anything official from the PQ team that guarantees this behavior?
Thanks,
Amon
There is no official statement from MS on this. The URL Greg_Deckler provided is accurate, but if you 100% need to know for a fact that is what happens, you will need to:
- Sort your data as desired.
- Group by the desired records
- Add an index
- expand the records
- Keep the first one (or last one, or whatever)
At that point there is no need to run Table.Distinct.
Note that Table.Distinct over an entire table this won't matter, but if you are using the optional columns, then it will.
Table.Distinct(Source) - won't matter
Table.Distinct(Source, {"Column1"}) - will matter
3 Replies
aseagull There is a whole discussion thread around this topic here: Which row will keep Table.Distinct(Table, {"Column... - Microsoft Power BI Community
There is an "official" answer from a v- if you scroll all the way down.
There is no official statement from MS on this. The URL Greg_Deckler provided is accurate, but if you 100% need to know for a fact that is what happens, you will need to:
- Sort your data as desired.
- Group by the desired records
- Add an index
- expand the records
- Keep the first one (or last one, or whatever)
At that point there is no need to run Table.Distinct.
Note that Table.Distinct over an entire table this won't matter, but if you are using the optional columns, then it will.
Table.Distinct(Source) - won't matter
Table.Distinct(Source, {"Column1"}) - will matter
Thank you Greg and Edhans both.
