String substraction from another string

[ValeriaBreve]

Hello,

I have a table as per below:

Main	Category	Category 2	Final
A	Apples=Pears	Pears,Raisin,Apple	Raisin
B	Red	Yellow,Green,Red	Yellow,Green
C	Seven=Five=Six	One,Two,Three,Four,Seven	One,Two,Three,Four

The Final table is what I need to achieve, so I need to substract each string in "Category" (between "=")  from the Category 2 if they are present.

Can you please h elp me? I am at a loss for ideas.

Thanks!

Kind regards

Valeria

[dufoq3 · Accepted solution]

Hi @ValeriaBreve, you made a mistake probably, so I've edited your sample data a bit

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HYq7CoAwFEP/5c75hQ4q6Kioi5QOggELpZWKj8/32iUh58RaqQRSHUegGbjmU1dpjKs/fURR4mClVjVy01wYQnrQZTLiR79uVEy8GU3rb5rJvwr6SMxPwrzrF226MspHnPsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Main = _t, Category = _t, #"Category 2" = _t]),
    Ad_Final = Table.AddColumn(Source, "Final", each 
        [ a = Text.Split([Category], "="),
          b = Splitter.SplitTextByAnyDelimiter(a)([Category 2]),
          c = Text.Combine(List.Select(List.Transform(b, (x)=> Text.Trim(x, {" ", ","})), (y)=> not List.Contains({"", null},y)))
        ][c], type text)
in
    Ad_Final

[ValeriaBreve]

Thanks @dufoq3 ! Yes you are right I made a spelling msitake in the data - sorry about that! And your solution works very well. Thanks! 🙂 

[dufoq3]

You're welcome Valeria.

[ValeriaBreve]

OK I think I got there...

= Table.AddColumn(#"Changed Type", "No_Match",
each
List.RemoveMatchingItems(
Text.Split([Category 2],","),
Text.Split([Category],"=")
)
)

But I am opne to any other suggestions 🙂

Thanks!