Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Delete/substract some string elements in columns

Hi Power BI-ers,

I have a table that contains these 2 columns.

The picture has been modified, so now it looks like there are some spaces before the comma, but actually there is none.

I would like to modify the Prod Reviewers column so that the MXXXXX that apear in the Critical users column are deleted from the Prod Reviewers column.

The goal is to have the following result (the crossed-out words are supposed to desapear of course)  :

I really have struggle manipulating the strings on Power BI, and I find the present problem complex as it asks to compare substrings and delete them.

Thanks in advance to the person who will find a solution to my problem ðŸ˜Š

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi Anonymous ,

     

    It's a bit complicated.

    1 Unpivot column Critical users and Prod ReviewersI.

     

    2 Create a measure like the following:

    Measure =
VAR _critical =
    VALUES ( 'Table'[unpivot Critical users] )
VAR _prod =
    VALUES ( 'Table'[Value] )
RETURN
    CONCATENATEX ( EXCEPT ( _prod, _critical ), [Value], "," )

     

    Result:

     

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    Hi Anonymous ,

     

    It's a bit complicated.

    1 Unpivot column Critical users and Prod ReviewersI.

     

    2 Create a measure like the following:

    Measure =
VAR _critical =
    VALUES ( 'Table'[unpivot Critical users] )
VAR _prod =
    VALUES ( 'Table'[Value] )
RETURN
    CONCATENATEX ( EXCEPT ( _prod, _critical ), [Value], "," )

     

    Result:

     

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi,

      Thanks a lot for your solution. 

      Indeed it does work like this, but my data is dynamic, which means there could be more than 4 or 5 "Prod Reviewers" and "Critical users". So how could we select the columns if their number is constantly changing ? Any alternative, maybe with a code ?
      Thank you in advance