Return complete String from multiple columns searched by a susbstring

[sparvez]

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.

[sparvez]

Many thanks @ImkeF 

[ImkeF · Accepted solution]

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

[sparvez]

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.

[ImkeF]

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