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

bobhasnoidea

New Member
Joined
Apr 9, 2019
Messages
3
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!
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,075
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]
 
Last edited:

bobhasnoidea

New Member
Joined
Apr 9, 2019
Messages
3
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

Well-known Member
Joined
Oct 24, 2015
Messages
7,075
You are welcome & thanks for the feedback
Have a nice day
 

Watch MrExcel Video

Forum statistics

Threads
1,113,931
Messages
5,545,091
Members
410,652
Latest member
Zot
Top