using Text.Proper in a condition

[delithyotho]

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! 😁

[SamInogic · Accepted 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: [email protected]
Service: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/