Forum Discussion

ValeriaBreve's avatar
ValeriaBreve
Post Partisan
2 years ago
Solved

String substraction from another string

Hello,

I have a table as per below:

 

MainCategoryCategory 2Final
AApples=PearsPears,Raisin,AppleRaisin
BRedYellow,Green,RedYellow,Green
CSeven=Five=SixOne,Two,Three,Four,SevenOne,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

 

Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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

     

4 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    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's avatar
      ValeriaBreve
      Post Partisan
      2 years ago

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

  • ValeriaBreve's avatar
    ValeriaBreve
    Post Partisan
    2 years ago

    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!