Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi Everyone,
Is there a workaround for this query:
Query M:
IF [Column1] = "sample1" THEN Text.Proper([Column2]) ELSE [Column2]
I am trying to Capitalize Each Word a column that is under a condition that all of them belongs to Column1 = sample1 only
My raw data looks like this:
Column1 | Column2 |
Sample1 | TEXT SAMPLE |
Sample2 | Text Sample |
Sample3 | T.E.X.T Sample |
Thank you in advance! 😁
Solved! Go to Solution.
Power BI DAX did not allow Text.Proper() in the expressions, so we can use the below workaround for this requirement to convert the Text from Column 2 as Proper Case (i.e. First letter of each word will be Capital).
Create a column in table with below DAX expression:
FormattedText = IF('Sample Table'[Column1] = "Sample1",
var SplitChar = " "
var column2Data = 'Sample Table'[Column2]
VAR ConvertedDataTable =
ADDCOLUMNS (
GENERATE (
CALCULATETABLE( 'Sample Table', 'Sample Table'[Column2] = column2Data ),
VAR TokenCount =
PATHLENGTH ( SUBSTITUTE ( 'Sample Table'[Column2] , SplitChar, "|" ) )
RETURN
GENERATESERIES ( 1, TokenCount )
),
"Proper",
var word = PATHITEM ( SUBSTITUTE ( 'Sample Table'[Column2] , SplitChar, "|" ), [Value] )
var word_Propper = UPPER( LEFT( word, 1 ) ) & LOWER( RIGHT( word, LEN( word) - 1 ) )
return
word_Propper
)
RETURN
CONCATENATEX( ConvertedDataTable, [Proper], " " ), 'Sample Table'[Column2])
Please refer to the below screenshot for the same,
The Output will be as follows:
If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.
Thanks!
Inogic Professional Services
An expert technical extension for your techno-functional business needs
Power Platform/Dynamics 365 CRM
Drop an email: crm@inogic.com
Service: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
Power BI DAX did not allow Text.Proper() in the expressions, so we can use the below workaround for this requirement to convert the Text from Column 2 as Proper Case (i.e. First letter of each word will be Capital).
Create a column in table with below DAX expression:
FormattedText = IF('Sample Table'[Column1] = "Sample1",
var SplitChar = " "
var column2Data = 'Sample Table'[Column2]
VAR ConvertedDataTable =
ADDCOLUMNS (
GENERATE (
CALCULATETABLE( 'Sample Table', 'Sample Table'[Column2] = column2Data ),
VAR TokenCount =
PATHLENGTH ( SUBSTITUTE ( 'Sample Table'[Column2] , SplitChar, "|" ) )
RETURN
GENERATESERIES ( 1, TokenCount )
),
"Proper",
var word = PATHITEM ( SUBSTITUTE ( 'Sample Table'[Column2] , SplitChar, "|" ), [Value] )
var word_Propper = UPPER( LEFT( word, 1 ) ) & LOWER( RIGHT( word, LEN( word) - 1 ) )
return
word_Propper
)
RETURN
CONCATENATEX( ConvertedDataTable, [Proper], " " ), 'Sample Table'[Column2])
Please refer to the below screenshot for the same,
The Output will be as follows:
If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.
Thanks!
Inogic Professional Services
An expert technical extension for your techno-functional business needs
Power Platform/Dynamics 365 CRM
Drop an email: crm@inogic.com
Service: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.