Power Query - Change value returned based on two columns and a date used.

[bobhasnoidea]

I’m only a few weeks in to my Power Query journey and have managed to get a number of complex tasks we do worked out, but I have hit a snag at the last hurdle!
I have a report produced by an external supplier that log the clocking in/out time of staff to numerous sites, this then gets merged with other sheets to record safety incidents etc. so far so good!
The issue I have come across is that I now need to split the name of one site based on the date as we need to separate the record of costs differently.
So in simple language
If [Site] = “Site x” and [In] <13/08/2020 23:59:59 then return “Site xa”
If [Site] = “Site x” and [In] >14/08/2020 00:00:0 then return “Site xb”
If [Site] = “Site A,B,C….,ZZZZ” then return “Site A,B,C….,ZZZ”
Can anyone give me any pointers on how to do this! It has got me stumped!

 

[sandy666]

maybe
If [Site] = “Site x” and [In] < "13/08/2020 23:59:59" then “Site xa” else If [Site] = “Site x” and [In] >"14/08/2020 00:00:0" then “Site xb” else If [Site] = “Site A,B,C….,ZZZZ” then “Site A,B,C….,ZZZ” else null

I assumed your datetime is a text (value) not a [column]

 

[sandy666]

btw. this is not golden advice

maybe try with Conditional Column

 

[bobhasnoidea]

Thanks Sandy. That got me where I needed to get to!
for any one else i ended up adding a column of pure date not DATETIME and then
if [Site]= "Site x" and [Date]<= #date(2020,8,13) then "Site Xa"
else if [Site]= "Site x" and [Date]>= #date(2020,8,14) then "Site Xb"
else [Site]
Thanks for the guidance.

 

[sandy666]

You are welcome & thanks for the feedback
Have a nice day