Transforming Data by parsing, transposing and pivoting data from a text column

BrianJ · October 31, 2020, 5:05pm

AntrikshSharma:

Table = 
VAR Temp =
    ADDCOLUMNS (
        ADDCOLUMNS (
            VALUES ( Ibesmond[TEXT] ),
            "CleanString",
                TRIM (
                    SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( Ibesmond[TEXT], "$", "^" ), " ", "^" ), ";", " " ), "^", " " ), "AA/BB", "AA BB" )
                )
        ),
        "DelimiterCount",
            1 + LEN ( [CleanString] ) - LEN ( SUBSTITUTE ( [CleanString], " ", "" ) )
    )

Discovering all sorts of gems here. Really like the nested ADDCOLUMNS that allows you to call a virtual column within the same table you created it. I’ve been doing this via sequential variables, but this is much cleaner.

One question - any objection to using the “@” prefix to denote a virtual column to distinguish it from a measure, as advocated by Ferrari? We’re trying to standardize on that approach in the forum solutions involving virtual tables.

Brian