Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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)
Solved! Go to 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 )
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 )
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"
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 )
It was a polite request , to know as in how this is working.
Can you please tell me how every line works in short?
@DJKarma , Can you please share expected output
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