Best solution (performance) to get list with unique values from two table columns

[JVos]

I have a table with 4 columns and 200 rows. There is a From_CoO and a To_CoO column. I want to get a list ('ListWithCoOs') of the unique values from both columns. E.g.:

- From_CoO has values: MEX, NLD, MEX, null, null, null

- To_CoO has values: SPA, NLD, null, NLD

then the result should be:

- ListWithCoOs: MEX, NLD, null [yes, even this one!], SPA

First I solved this with the following code:

//Get from each column unique values, combine unique values and get unique values of combined list
TestInput = Table.Buffer(Source{[Name="TestInput"]}[Content]),
ListWithCoOs = List.Distinct(List.Combine({List.Distinct(TestInput[From_CoO]), List.Distinct(TestInput[To_CoO])})),

However performance was bad. So I changed it into the following:

TestInput = Table.Buffer(Source{[Name="TestInput"]}[Content]),
#"Removed Duplicates" = Table.Distinct(TestInput, {"From_CoO"}),
listFrom = List.Distinct(#"Removed Duplicates"[From_CoO]),
#"Removed Duplicates2" = Table.Distinct(TestInput, {"To_CoO"}),
listTo = List.Distinct(#"Removed Duplicates"[To_CoO]),
ListWithCoOs = List.Distinct(List.Combine({listFrom, listTo})),

This performs much better! But why? It are only 200 records. When in the Power Query user interface (Power Query Editor) I click the button on top of the column, in no time the unique values are shown.

[dax]

Hi JVos, 

I think this is similar to DAX Nested iterators, which will be bad performance when iterators is large. And when combine, it seems will loop entire list or table which might cause more time(which might cause query 1 slower than query 2). Or you also could ask @ImkeF  for more M code performance suggestions and knowledge.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ImkeF]

I have no idea about performance of this pattern, sorry.