Forum Discussion
jcastr02
Post Prodigy
Post ProdigyCustom 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...
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.
Omid_Motamedise
Super User
Super UserJust 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!
jcastr02Apologies, 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?