Forum Discussion
Solved
Dax measure needed
Hi! I need help formatting a text column. It seems so easy to do it in SQL using string functions, but I need help doing it using DAX: I have a column that has two problems - each value is a list ...
I realized you wanted DAX so here they are
CapitalizedFirstTwoCharacters CalcColumn = VAR CountOfComma = LEN ( 'Table'[MyColumn] ) - LEN ( SUBSTITUTE ( 'Table'[MyColumn], ",", "" ) ) VAR Numbers = GENERATESERIES ( 1, 2, 1 ) VAR Substituted = SUBSTITUTE ( 'Table'[MyColumn], ",", "|" ) VAR _Names = ADDCOLUMNS ( Numbers, "@name", VAR _item = PATHITEM ( Substituted, [Value] ) VAR _itemLength = LEN ( _item ) RETURN UPPER ( LEFT ( _item, 2 ) ) & LOWER ( RIGHT ( _item, _itemLength - 2 ) ) ) RETURN CONCATENATEX ( _Names, [@name], ", " )CapitalizedFirstTwoCharacters Measure = VAR _Value = MAX ( 'Table'[MyColumn] ) VAR CountOfComma = LEN ( _Value ) - LEN ( SUBSTITUTE (_Value, ",", "" ) ) VAR Numbers = GENERATESERIES ( 1, CountOfComma, 1 ) VAR Substituted = SUBSTITUTE (_Value, ",", "|" ) VAR _Names = ADDCOLUMNS ( Numbers, "@name", VAR _item = PATHITEM ( Substituted, [Value] ) VAR _itemLength = LEN ( _item ) RETURN UPPER ( LEFT ( _item, 2 ) ) & LOWER ( RIGHT ( _item, _itemLength - 2 ) ) ) RETURN CONCATENATEX ( _Names, [@name], ", " )
Hi E12345
Add this custom column in the query editor:
let
// Split the text in MyColumn using commas as the delimiter
SplitText = Text.Split([MyColumn], ","),
// Transform each part by uppercasing the first two characters and lowercasing the rest
UppercasedFirstTwoChars =
List.Transform(SplitText, each Text.Upper(Text.Start(_,2)) & Text.Lower(Text.End(_, Text.Length(_)-2))),
// Recombine the transformed parts into a single text, joined by commas
Recombined = Text.Combine(UppercasedFirstTwoChars, ", ")
in
// Output the recombined text
Recombined
I realized you wanted DAX so here they are
CapitalizedFirstTwoCharacters CalcColumn =
VAR CountOfComma =
LEN ( 'Table'[MyColumn] ) - LEN ( SUBSTITUTE ( 'Table'[MyColumn], ",", "" ) )
VAR Numbers =
GENERATESERIES ( 1, 2, 1 )
VAR Substituted =
SUBSTITUTE ( 'Table'[MyColumn], ",", "|" )
VAR _Names =
ADDCOLUMNS (
Numbers,
"@name",
VAR _item =
PATHITEM ( Substituted, [Value] )
VAR _itemLength =
LEN ( _item )
RETURN
UPPER ( LEFT ( _item, 2 ) )
& LOWER ( RIGHT ( _item, _itemLength - 2 ) )
)
RETURN
CONCATENATEX ( _Names, [@name], ", " )
CapitalizedFirstTwoCharacters Measure =
VAR _Value = MAX ( 'Table'[MyColumn] )
VAR CountOfComma =
LEN ( _Value ) - LEN ( SUBSTITUTE (_Value, ",", "" ) )
VAR Numbers =
GENERATESERIES ( 1, CountOfComma, 1 )
VAR Substituted =
SUBSTITUTE (_Value, ",", "|" )
VAR _Names =
ADDCOLUMNS (
Numbers,
"@name",
VAR _item =
PATHITEM ( Substituted, [Value] )
VAR _itemLength =
LEN ( _item )
RETURN
UPPER ( LEFT ( _item, 2 ) )
& LOWER ( RIGHT ( _item, _itemLength - 2 ) )
)
RETURN
CONCATENATEX ( _Names, [@name], ", " )
Thank you so much! You are so close, but this function drops one of the names on the list as you can see from your data sample (there is always one name missing if there is more than one name on the line). Can this function be updated? TY!
