Forum Discussion

sparvez's avatar
sparvez
Helper I
3 years ago
Solved

Return complete String from multiple columns searched by a susbstring

Hello Altruists,

 

I am in a complex situation trying to solve for last few days, but see no hope. Can anyone pls help me?

 

In a table, i have many columns, column numbers are unknown. Only one cell have the info of "TV".  I need to get the complete string of TV , by using substring "TV" in the newly added column columntoFindxxTV . How to do it pls ?

 

column1---column2---column3---...columnX---columntoFindxxTV
1xTV---1xRadio---2xLaptop---...(null)------1xTV
(null)---3xIpad---2xTV---...(null)---2xTV
1xbook---5xRadio---2xPhone---...5xTV--5xTV

 

thank you in advance.

Need Help
  • ImkeF's avatar
    ImkeF
    3 years ago

    Hi sparvez ,
    great! You can rewrite like so:
    List.First(List.Select(Record.FieldValues(_), (l)=> Text.Contains(Text.Upper(Text.From(l)), "TV")))

     

4 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    3 years ago

    Hi sparvez ,
    great! You can rewrite like so:
    List.First(List.Select(Record.FieldValues(_), (l)=> Text.Contains(Text.Upper(Text.From(l)), "TV")))

     

  • ImkeF's avatar
    ImkeF
    Community Champion
    3 years ago

    Hi sparvez ,
    you add a custom column with this  formula:
    List.Select(Record.FieldValues(_), (l)=> Text.Contains(Text.Upper(Text.From(l)), "TV")){0}

  • sparvez's avatar
    sparvez
    Helper I
    3 years ago

    It works ! thanks so much. Just one small thing, if the substring is abesnt, it needs to return 0 , how can I add it ?

    Now, it is showing "Error"  if substring is absent.