Help with M Code Syntax | IF Statement based on a couple of columns | Data Cleansing

[Anonymous]

Hello M Code experts,

In need of some assistance here. I have a total of 8 CSV files containing Queue Log Data. For whatever reason, our client's database has a few mistakes/errors involving Start and End dates being swaped on and before the date of 4/9/2021 for 4 of those CSV files. Because of this happening, I recieve an error because the transaction duration is a negativwe number which makes no sense.

Here is what I want to do:

- In column "Source.Name" we have 4 CSV Files with this mix-up error - "WB_Dispatcher-items.csv", "WB_ExceptionQueue-itmes.csv", WB_ProcessorQueue-items.csv", and "WB_SendInvoice-items.csv"

- For all of these Source.Files, all Queue log items/transactions on and before 4/9/2021 are messed up because the Start and End Date/Time are swapped which is why I am facing this error.

- I'd like to create an IF statemenent for a brand new Start and END column with all create values

- IF Source.NAME = "WB_Dispatcher-items.csv", "WB_ExceptionQueue-itmes.csv", WB_ProcessorQueue-items.csv", and "WB_SendInvoice-items.csv" AND Start Column <= 04/09/2021 23:59 THEN RETURN the END Date column values(date/time), ELSE return START Date for all of the rest of the line items which are all correct.

- I would do this both for a new Start Column and End column

Here is what I've started with, but have no idea how to correctly finish:

IF [Source.Name]= "WB_Dispatcher-items.csv" or "WB_ExceptionQueue-items.csv" or "WB_ProcessorQueue-items.csv" or "WB_SendInvoice-items.csv" and [Started] <= dateDateTime.Time(04/09/2021) then [Ended] else [Started]

I haven't had too much experience in writing any M Code, but have used Power Query's easy UI to do most of my basic ETL/Data Engineering-like things. It's looking like I'll be going into more depth of M CODE in the near future.

If anyone can help me out here, that would be great. All the best.

[Anonymous]

I must be slipping, my code is not even code! Try:

Table.AddColumn(Table, "CorrectedStart", each if ([Source.Name] = "WB_Dispatcher-items.csv" or [Source.Name] = "WB_ExceptionQueue-items.csv" or [Source.Name] ="WB_ProcessorQueue-items.csv" or [Source.Name] ="WB_SendInvoice-items.csv") and [Started] <= #datetime(2021,4,9,0,0,0) then [Ended] else [Started]

Question for you, would

each if [StartTime] > [EndTime] then [Endtime] else [StartTime]

be easier?

[Anonymous]

This was perfect. Thank you so much for your expertise.

[Anonymous]

Well, M allows us to compare date to DateTime, but just to be sure, change it to:

if ([Source.Name] = "WB_Dispatcher-items.csv" or "WB_ExceptionQueue-items.csv" or "WB_ProcessorQueue-items.csv" or "WB_SendInvoice-items.csv") and [Started] <= #datetime(2021,4,9,0,0,0) then [Ended] else [Started]

So now it's a datetime instead of a date.

--Nate

[Anonymous · Accepted solution]

Nate - thanks so much. This perfectly worked out when the only 4 available fields in source.name was one of the 4 flawed CSVs. When I try to use this on the whole dataset including the other Queue Names in the source.name column, I get errors. Is there a way to include in the function that "if any other name shows up in source.name, then just bring back the normal START date?

[Anonymous]

You are on the right track, just some syntax:

if ([Source.Name] = "WB_Dispatcher-items.csv" or "WB_ExceptionQueue-items.csv" or "WB_ProcessorQueue-items.csv" or "WB_SendInvoice-items.csv") and [Started] <= #date(2021,4,9) then [Ended] else [Started]

So IF should be "if", and a date literal is #date(y,m,d).  Added parentheses to the multiple "or" so that the last "or" doesnt become entangled with the "and".

--Nate

[Anonymous]

Awesome - thank you for the quick reply. Just one question, my Start and End dates contain Time as well like so: "4/30/2021 1:57:00 PM" - Will I still be able to use that same syntax? I would assume not..