topic Adding space between texts using Uppercase as an indicator of where the space should be in DAX Commands and Tips

Adding space between texts using Uppercase as an indicator of where the space should be

JuggernautProf — Mon, 04 Sep 2023 03:29:43 GMT

Hi PowerBI community,

I am keen to understand if it is possible to add space between texts using Uppercase as an indicator of where the space should be:

For instance: "IronmanMarvelSuperhero" to "Ironman Marvel Superhero"

Is this possible? if it is how do we do this in DAX?

Re: Adding space between texts using Uppercase as an indicator of where the space should be

OwenAuger — Mon, 04 Sep 2023 04:36:59 GMT

Hi JuggernautProf

This would probably be best handled further upstream, in data source or Power Query.

However, something like this is possible with DAX.

Here is the code for a calculated column (PBIX attached):

Text With Spaces = 
VAR CurrentText = 'Text'[Text]
VAR SpaceCode = UNICODE ( " " )
VAR UpperMin = UNICODE ( "A" )
VAR UpperMax  = UNICODE ( "Z" )
VAR NumChars = LEN ( CurrentText )
VAR PositionCode =
    SELECTCOLUMNS (
        GENERATESERIES ( 1, NumChars ),
        "@Position", [Value],
        "@Code", UNICODE(MID ( CurrentText, [Value], 1 ))

    )
VAR UpperPositionCode =
    FILTER (
        PositionCode,
        VAR IsUpper =
            [@Code] >= UpperMin
                && [@Code] <= UpperMax
                && [@Position] > 1
        RETURN
            IsUpper
    )
VAR Spaces =
    SELECTCOLUMNS (
        UpperPositionCode,
        "@Position", [@Position] - 0.5,
        "@Code", SpaceCode
    )
VAR NewPositionCode =
    UNION ( PositionCode, Spaces )
VAR Result =
    CONCATENATEX (
        NewPositionCode,
        UNICHAR ( [@Code] ),
        "",
        [@Position]
    )
RETURN 
    Result

The basic idea is to find the uppercase characters in all positions after the first, then insert a space before each of them and reassemble.

Regards

Re: Adding space between texts using Uppercase as an indicator of where the space should be

JuggernautProf — Tue, 05 Sep 2023 00:50:58 GMT

Hey OwenAuger Thanks. I tried this, however I keep getting an error "DAX comparison pperations do not support comparing values of type Text with values of type integer. Consider using VALUE or FORMAT function to conver one of the values.

Also in addition to the above, I keep seeing an error with [@code], [@position], "cannot find name [@code]"

Re: Adding space between texts using Uppercase as an indicator of where the space should be

OwenAuger — Tue, 05 Sep 2023 01:32:19 GMT

Hey again JuggernautProf

That's odd - you shouldn't receive those errors if the code has been placed in a calculated column. Then only dependency on the existing model is the reference to 'Text'[Text] at the start.

One thing we may need to consider is handling of blank/empty strings.

Could you share a link to a sanitised PBIX demonstrating the issue (OneDrive, Google Drive etc), or a screenshot of the Power BI interface showing the table where you're adding the column?

Re: Adding space between texts using Uppercase as an indicator of where the space should be

Oleh116611 — Fri, 11 Oct 2024 12:47:44 GMT

Thank you very much OwenAuger , the solution works perfectly!