New Column to Transform Data: Need DAX Help

[kault]

I have one column where the data output is "AccountName:ProjectName". I need to create a new column that pulls ONLY the account name, so everything before the colon (:), if there is a colon. If there is no colon, I want it to pull the full value. What would be the formula for this new column?

AccountName:ProjectName (current)	AccountName (want)
Hospital A:Medical Supplies	Hospital A
School X	School X

[v-eqin-msft]

Hi @kault ,

According to my understanding, you want to extract the letters before the “:”, right?

I did it in two ways.

1.Use M formula -- Text.BeforeDelimiter( ) in Query Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sgvLsgsScyx8k1NyUxOzFEILi0oyMlMLVaK1YlWCk7OyM/PUYhQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"AN:PN" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"AN:PN", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.BeforeDelimiter([#"AN:PN"],":"))
in
    #"Added Custom"

2.Use DAX:

Expected =
VAR valueLength =
    IFERROR (
        SEARCH ( ":", SELECTEDVALUE ( Split[AN:PN] ) ) - 1,
        LEN ( SELECTEDVALUE ( Split[AN:PN] ) )
    )
RETURN
LEFT ( SELECTEDVALUE ( Split[AN:PN] ), valueLength )

Is the result what you want? If you have any questions, please upload some data samples and expected output.

Please do mask sensitive data before uploading.

Best Regards,

Eyelyn Qin

[pranit828 · Accepted solution]

HI @kault 

I would create a column using

IF(CONTAINSSTRING('Table'[AccountName:ProjectName (current)],":"),LEFT('Table'[AccountName:ProjectName (current)],FIND(":",'Table'[AccountName:ProjectName (current)])-1),'Table'[AccountName:ProjectName (current)])

[MFelix]

Hi @kault,

I would do this in the power query, but see the solution below for both DAX and Power Query:

Power Query

if Text.PositionOf ([#"AccountName:ProjectName (current)"], ":") < 0 then [#"AccountName:ProjectName (current)"] else Text.AfterDelimiter ([#"AccountName:ProjectName (current)"], ":")

DAX

Column =
IF (
    FIND ( ":", 'Table'[AccountName:ProjectName (current)],, 0 ) = 0,
    'Table'[AccountName:ProjectName (current)],
    RIGHT (
        'Table'[AccountName:ProjectName (current)],
        LEN ( 'Table'[AccountName:ProjectName (current)] )
            - FIND ( ":", 'Table'[AccountName:ProjectName (current)],, 0 )
    )
)

[MFelix]

Hi @kault

Give you incorrect formula

Column =
IF (
FIND ( ":", 'Table'[AccountName:ProjectName (current)],, 0 ) = 0,
'Table'[AccountName:ProjectName (current)],
LEFT (
'Table'[AccountName:ProjectName (current)],
LEN ( 'Table'[AccountName:ProjectName (current)] )
- 1
)
)

Regarding the power query you should add a new column and place the fornula

[kault]

 Thank you. I couldn't get the DAX formula to work - it gave me everything to the right of the column instead of the left of the column. I can try it in power query editor, but I'm not sure what to do. Where do I enter this?

if Text.PositionOf ([#"AccountName:ProjectName (current)"], ":") < 0 then [#"AccountName:ProjectName (current)"] else Text.AfterDelimiter ([#"AccountName:ProjectName (current)"], ":")