Custom column based on whats contained in a row

[jcastr02]

I was trying to create a custom column in power query (a yes/no column is fine)   that if any of the values in the row contains *00:00, the FE Yes/No column would be "No, if not then "Yes"  see below data example and desired result in last column

Location number

FE Sun Open

FE Sun Close

FE Mon Open

FE Mon Close

FE Tue Open

FE Tue Close

FE Wed Open

FE Wed Close

FE Thu Open

FE Thu Close

FE Fri Open

FE Fri Close

FE Sat Open

FE Sat Close

FE Yes/No

2692

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

No

2761

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

No

3145

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

No

3181

8:00

22:00

7:00

22:00

7:00

22:00

7:00

22:00

7:00

22:00

7:00

22:00

8:00

22:00

Yes

3400

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

No

3446

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

No

3689

9:00

21:00

7:00

21:00

7:00

21:00

7:00

21:00

7:00

21:00

7:00

21:00

7:00

21:00

Yes

4212

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

No

4549

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

No

4768

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

No

5192

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

No

6072

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

No

6191

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

*00:00

No

6564

*08:00

*22:00

*08:00

*22:00

*08:00

*22:00

*08:00

*22:00

*08:00

*22:00

*08:00

*22:00

*08:00

*22:00

Yes

6831

7:00

24:00:00

7:00

24:00:00

7:00

24:00:00

7:00

24:00:00

7:00

24:00:00

7:00

24:00:00

7:00

24:00:00

Yes

[v-bofeng-msft · Accepted solution]

Hi @jcastr02 ,

Please try:

= Table.AddColumn(#"Changed Type", "Custom", each if List.ContainsAny({Record.Field(_, "FE Sat Open"), Record.Field(_, "FE Sat Close")}, {"*00:00"}) then "Yes" else "No")

Best Regards,

Bof

[v-bofeng-msft]

Hi @jcastr02 ,

I've made a test for yor reference:

if List.ContainsAny(Record.FieldValues(_), {"*00:00"}) then "Yes" else "No"

Best Regards,

Bof

[jcastr02]

Apologies, I should have clarified - what if I Only wanted to check within this table for specific columns, such as FE Sat Open and FE Sat Close (as example) - how could I tailor to list out the specific columns?

[v-bofeng-msft · Accepted solution]

Hi @jcastr02 ,

Please try:

= Table.AddColumn(#"Changed Type", "Custom", each if List.ContainsAny({Record.Field(_, "FE Sat Open"), Record.Field(_, "FE Sat Close")}, {"*00:00"}) then "Yes" else "No")

Best Regards,

Bof

[jcastr02]

Hello @v-bofeng-msft Thanks so much.  I tried to enter this , however getting different answers when the values are the same in the rows.  Any ideas?  Thanks so much.

[AntrikshSharma · Accepted solution]

Create a new step by clicking FX button and then paste that code in Editor, what you're doing is pasting the code in a custom column.

[Omid_Motamedise]

Just copy and past the following code into the Advance editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zZSxDoMwDET/JSNisI1zsfmI7hVi7Nyh/y+VEiK1ZUXgKfENyctFd9OUBC6pTx3RSHTV5vZMc7+wFHAUloE1x2Gxjy9WRZG6luPGv5Pvj1e9V697+s4DVYRhgfky+eYa/3p6/Nj+Q4XDZFWzehiWAovCkjlOn4JKHBb2MN2ODF3lrfe6VnxnKi3UsIG/Iq9jgz1TWWHmNw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Location number" = _t, #"FE Sun Open" = _t, #"FE Sun Close" = _t, #"FE Mon Open" = _t, #"FE Mon Close" = _t, #"FE Tue Open" = _t, #"FE Tue Close" = _t, #"FE Wed Open" = _t, #"FE Wed Close" = _t, #"FE Thu Open" = _t, #"FE Thu Close" = _t, #"FE Fri Open" = _t, #"FE Fri Close" = _t, #"FE Sat Open" = _t, #"FE Sat Close" = _t, #"FE Yes/No" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Yes/No", each if Text.Contains(Text.Combine(List.Transform(Record.ToList(_),Text.From),"|"),"*00:00") then "No" else "Yes")
in
    #"Added Custom"

If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. 

Thank you!

[jcastr02]

Apologies, I should have clarified - what if I Only wanted to check within this table for specific columns, such as FE Sat Open and FE Sat Close (as example) - how could I tailor to list out the specific columns?