Forum Discussion
Help with M Code Syntax | IF Statement based on a couple of columns | Data Cleansing
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.
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?
6 Replies
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
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?
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
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?