Forum Discussion
Custom column based on whats contained in a row
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 |
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
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.
7 Replies
Hi jcastr02 ,
I've made a test for yor reference:
if List.ContainsAny(Record.FieldValues(_), {"*00:00"}) then "Yes" else "No"Best Regards,
Bof
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?
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
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!
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?
