Custom Column Based on Distinct Count

[Thad]

I CANNOT believe that the answer to this question is NOWHERE to be found on the internet, but here we are. 

I am trying to create a custom column in Power Query with the contents depending on the distinct count in a certain column called "Organisationseinheit". The column has to have at least 5 different values in it to be used further.

If it was a measure, it'd go like this:

If(DISTINCTCOUNT('Fragenkatalog'[Organisationseinheit]) <5, "Keine Auswahlmöglichkeit", 'Fragenkatalog'[Organisationseinheit]))

For some reason, I can't find ANY explanation on how the syntax is supposed to work for distinct counts in power query formulas. Also, I cannot find any way to use the result of the "Count distinct rows"-function from the transform ribbon, since it gives you the desired result but there's seemingly no way to use that result any further. 

This is such a laughably simple task, what the hell am I doing wrong?!

[j_ocean]

There's probably a way to do it within one query using advanced editor, but it might be easier use the GUI:

1. Referece Query A into Query B
2. Group-By Query B to whatever you need
3. Reference query A into Query C
4. Merge C and B
5. Disable load on A and B

[Anonymous]

if Table.RowCount(Table.Distinct(Fragenkatalog, {"ColumnName"})) <5 then "Keine Auswahlmöglichkeit" else  [Organisationseinheit]

--Nate

[Thad · Accepted solution]

That one gave me the following Error:

Expression.Error: A cyclic reference was encountered during evaluation.

I found a workaround tho by creating a custom column like this:

= Table.AddColumn(#"Gefilterte Zeilen", "Orgaeinheit Ausprägungen", each List.NonNullCount(List.Distinct(#"Gefilterte Zeilen"[Organisationseinheit])))

and then another custom column like this: 

= Table.AddColumn(#"Added Custom", "Orgaeinheit", each if [Orgaeinheit Ausprägungen] < 5 then "Keine Auswahlmöglichkeit" else [Organisationseinheit])

which gave me the desired outcome. 

Thanks anyway!