Create a conditional column based on contents of data

[DJKarma]

This is a simple request.
I have to create a column in query editor, or in table view. Whichever is easy.

Column looks like this -->

A,B,C,D,D,E,D

B,C,D,B,D,A

C,C,D,F,E,G

D,D,E,E,E,F,B

Result should be based on count of characters present, with 'A' character always taking the priority.
For instance result of the above column next to it will be

A ( A will take priority even if D has most count)

A (Even though B has most count,  A will take Priority)

C ( as C has most count)

E ( as E has most count)

[tamerj1 · Accepted solution]

hI @DJKarma 
Please refer to attached file with the solution 

Result = 
VAR Items = SUBSTITUTE ( 'Table'[Column], ",", "|" )
VAR Length = PATHLENGTH ( Items )
VAR T1 = GENERATESERIES ( 1, Length, 1 )
VAR T2 = ADDCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) )
VAR T3 = GROUPBY ( T2, [@Item], "@Count", COUNTX ( CURRENTGROUP(), 1 ) )
VAR T4 = TOPN ( 1, T3, [@Count] )
VAR Result = MAXX ( T4, [@Item] )
RETURN
    IF ( PATHCONTAINS ( Items, "A" ), "A", Result )

[DJKarma]

Thanks, I am not able to check this, as there are also blanks in my column which I cant remove.
Can you edit your code to handle blanks as well?
If blank then output "XX"

[tamerj1]

Hi @DJKarma 
See attached

Result = 
VAR String = COALESCE ( 'Table'[Column], "XX" )
VAR Items = SUBSTITUTE ( String, ",", "|" )
VAR Length = PATHLENGTH ( Items )
VAR T1 = GENERATESERIES ( 1, Length, 1 )
VAR T2 = ADDCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) )
VAR T3 = GROUPBY ( T2, [@Item], "@Count", COUNTX ( CURRENTGROUP(), 1 ) )
VAR T4 = TOPN ( 1, T3, [@Count] )
VAR Result = MAXX ( T4, [@Item] )
RETURN
    IF ( PATHCONTAINS ( Items, "A" ), "A", Result )

[DJKarma]

It was a polite request , to know as in how this is working.
Can you please tell me how every line works in short?

[tamerj1]

Hi @DJKarma 
I'm returning the variables one by one for better understanding

[amitchandak]

@DJKarma , Can you please share expected output

[DJKarma]

A ( A will take priority even if D has most count)

A (Even though B has most count,  A will take Priority)

C ( as C has most count)

E ( as E has most count)

Just the characters from the column, i have written reasons in the bracket which you can exclude