Power Query Comparing Dates and returning late, early, or on time in a custom column

[SteynBS]

Hi guys,

I need some assistance as I am fairly new to power query.

I have 2 columns with dates Posting Date(Delivery Date) and Calculated Delivery date. If the Posting date is before the calculated delivery date it should return a value in a custom column Stating "early". If the dates are the same it should state "on time" and if the posting date is later than the calculated date, it should return "late" Any assistance please?

I need some advice on adding working days (provided in a column) to a date provided in another column. I have used the Date.AddDays([PO Date],[Planned Delivery Time), but this is adding calendar days and not working days.

Any assistance will be highly appreciated. Thank you

 

[jdellasala]

Using just the PQ Editor interface, using the Conditional Column in the Add Column tab turns this:

Book1
	A	B
1	Posting Date	Delivery Date
2	01/29/2023	01/17/2023
3	03/07/2023	03/08/2023
4	03/11/2023	02/02/2023
5	08/01/2023	08/01/2023
6	08/12/2023	02/10/2023
7	08/17/2023	08/17/2023
8	08/28/2023	05/27/2023
9	08/31/2023	07/13/2023
10	11/20/2023	11/21/2023
11	12/18/2023	10/15/2023
Sheet1

into this:

Book1
	D	E	F
1	Posting Date	Delivery Date	Status
2	01/29/2023	01/17/2023	Early
3	03/07/2023	03/08/2023	Late
4	03/11/2023	02/02/2023	Early
5	08/01/2023	08/01/2023	On Time
6	08/12/2023	02/10/2023	Early
7	08/17/2023	08/17/2023	On Time
8	08/28/2023	05/27/2023	Early
9	08/31/2023	07/13/2023	Early
10	11/20/2023	11/21/2023	Late
11	12/18/2023	10/15/2023	Early
Sheet1

M Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Dates"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Posting Date", type date}, {"Delivery Date", type date}}),
    AddedStatusColumn = Table.AddColumn(ChangedType, "Status", each if [Delivery Date] < [Posting Date] then "Early" else if [Delivery Date] = [Posting Date] then "On Time" else if [Delivery Date] > [Posting Date] then "Late" else null),
    ChangedType1 = Table.TransformColumnTypes(AddedStatusColumn,{{"Status", type text}})
in
    ChangedType1