Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

New Column to Transform Data: Need DAX Help

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 SuppliesHospital A
School XSchool X
  • Anonymous's avatar
    Anonymous
    6 years ago

    HI Anonymous 

     

    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)])

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI Anonymous 

     

    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's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    Hi Anonymous,

     

    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 )
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

       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)"], ":")

       

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      6 years ago

      Hi Anonymous

       

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

    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