Forum Discussion

MikeGanem3's avatar
MikeGanem3
Icon for Helper I rankHelper I
1 year ago
Solved

Power Query formula to get text after last comma - BI Service

I can do it in Desktop using "Add Column" in "Transform Data" but need to get it done in Service. If the column value is         Help, Tips, Tutorial I need to extract Tutorial and put it in a new...
General Comment
Need Help
  • v-hashadapu's avatar
    v-hashadapu
    1 year ago

    Hi MikeGanem3 , Thank you for reaching out to the Microsoft Community Forum.

     

    In Power BI Service, you can't modify datasets directly like you do in Power BI Desktop using Power Query, However, you can achieve this using DAX in a calculated column or measure.

     

    1. Example for Column:

    NewColumn =

    VAR TextList = 'YourTable'[YourColumn]  -- Replace with your actual table and column name

    VAR LastValue = TRIM(RIGHT(TextList, LEN(TextList) - FIND("@", SUBSTITUTE(TextList, ", ", "@", LEN(TextList) - LEN(SUBSTITUTE(TextList, ", ", ""))))))

    RETURN LastValue

     

    1. Example for Measure:

    LastValueMeasure =

    VAR TextList = SELECTEDVALUE('YourTable'[YourColumn])

    VAR LastValue = TRIM(RIGHT(TextList, LEN(TextList) - FIND("@", SUBSTITUTE(TextList, ", ", "@", LEN(TextList) - LEN(SUBSTITUTE(TextList, ", ", ""))))))

    RETURN LastValue

     

    If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
    Thank you.

Fowmy's avatar
Fowmy
Icon for Super User rankSuper User
1 year ago

MikeGanem3 

Under, Add column, use Extract > Text .AfterDelimiter choose the the following options

 

  • MikeGanem3's avatar
    MikeGanem3
    Icon for Helper I rankHelper I
    1 year ago

    I know how to do it in Desktop and already mentioned in my original post, my question was how to do it in BI Service

    • v-hashadapu's avatar
      v-hashadapu
      Icon for Community Support rankCommunity Support
      1 year ago

      Hi MikeGanem3 , Thank you for reaching out to the Microsoft Community Forum.

       

      In Power BI Service, you can't modify datasets directly like you do in Power BI Desktop using Power Query, However, you can achieve this using DAX in a calculated column or measure.

       

      1. Example for Column:

      NewColumn =

      VAR TextList = 'YourTable'[YourColumn]  -- Replace with your actual table and column name

      VAR LastValue = TRIM(RIGHT(TextList, LEN(TextList) - FIND("@", SUBSTITUTE(TextList, ", ", "@", LEN(TextList) - LEN(SUBSTITUTE(TextList, ", ", ""))))))

      RETURN LastValue

       

      1. Example for Measure:

      LastValueMeasure =

      VAR TextList = SELECTEDVALUE('YourTable'[YourColumn])

      VAR LastValue = TRIM(RIGHT(TextList, LEN(TextList) - FIND("@", SUBSTITUTE(TextList, ", ", "@", LEN(TextList) - LEN(SUBSTITUTE(TextList, ", ", ""))))))

      RETURN LastValue

       

      If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
      Thank you.