Power Query Get Duration Null Date

[svacharya]

I have a data set in CSV format that has multiple date fields. I have loaded the data into Power Query and set the Data Type to date. I am attempting to calculate the Duration (in days) between two dates. I get bunch of errors because some of the values in the Date columns are null. Is there a better way to calculate Duration between two dates? Below is the PQ code that i used.

Table.AddColumn(#"Changed Type1", "xDuration", each Duration.Days([[FINAL_INSTRELEASEDATE]] - [HNSUPLOADDT]))

 

[alansidman]

I can think of two options since you did not tell us what you want to happen when there is a null value. Should it be ignored? then look at my #2. If you wish to have it be zero then look at my #1.

1. For the nulls, do a replace and replace them with the date value you are comparing to so that you end up with a zero value.
2. Filter out the rows with null values.

Without more information on what you want and actual examples shown, this is the best I can offer.

 

[svacharya]

Thank you for taking the time to respond. Below is a description of the data and what i am attempting to do (highlevel).

1. My dataset tracks progress of products through various "gates" for customers.
2. There are multiple customers and a finite number of products (say 10).
3. Each product has four possible gates to traverse (as an example receive , build , Quality Control and ship) and therefore 4 completion dates (represented in columns).
4. I have attached an image of a sample data set
5. The end result is to graphically represent elapsed time between tasks for a given product/Customer. I have attached the output as well.
6. i want to determine the duration between Task1 and Task2. Task2 and Task3. Task3 and Task4.
7. I cannot filter out the Nulls because the record is valid.
8. If a date is null, i would like the duration calculation to be either blank or null (not zero)

I have built this successfully in excel (non PQ) using formulas etc. To mitigate null (non PQ), i check for "null"condition and calculate the duration. But i prefer to load and manipulate the data in power query due to the sheer number of records.

i have not been successful in checking for null and setting the value accordingly in PQ. I have NOT tried error handling (equivalent of IFERROR) as possible solution. I am novice with M Code and still trying to figure this out.

Appreciate any guidance! Thanks in advance!

 

[alansidman]

Cannot manipulate Data in a picture. Please use the XL2BB function to provide data. Help us to help you by giving us something we can work with and not require us to recreate your data to fix your problem.

 

[svacharya]

Attached

Book1
	A	B	C	D	E	F	G
1	Customer	Product	Task1	Task2	Task3	Task4
2	Customer1	Product1	9/1/20 4:00	9/3/20 12:30	10/13/20 12:47
3	Customer1	Product2	11/12/20 5:00	11/14/20 9:53	11/16/20 9:55	12/2/20 11:11
4	Customer1	Product4	12/3/20 5:00	12/8/20 13:19	12/22/20 11:59
5	Customer2	Product4	1/4/21 0:00	1/4/21 13:01	2/15/21 18:18	2/24/21 12:31
6	Customer3	Product3	1/4/21 0:00	1/4/21 13:01
7	Customer4	Product2	8/3/20 4:00	8/4/20 12:15
8	Customer5	Product1	8/3/20 4:00	8/6/20 12:15
9	Customer6	Product3	8/3/20 4:00	8/8/20 12:15	8/22/20 12:20	8/28/20 15:56
10
11
12
13	Customer	Elapsed Time1	Elapsed Time2	Elapsed Time 3
14	Customer1	2	40
15	Customer1	2	2	16
16	Customer1	5	14
17	Customer2	1	42	9
18	Customer3	1
19	Customer4	1
20	Customer5	3
21	Customer6	5	14	6
22
23
Sheet1

Cell Formulas
Range	Formula
D15,B21:D21,B14:B20,C17:D17,C14:C16	D15	=F3-E3

 

[alansidman]

I have achieved your end result using Power Query. Here are two different ways to get it

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Product", type text}, {"Task1", type number}, {"Task2", type number}, {"Task3", type number}, {"Task4", type number}}),
    #"Inserted Subtraction" = Table.AddColumn(#"Changed Type", "Subtraction", each [Task2] - [Task1], type number),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Subtraction",{{"Subtraction", "Elapsed Time1"}}),
    #"Inserted Subtraction1" = Table.AddColumn(#"Renamed Columns", "Subtraction", each [Task3] - [Task2], type number),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Subtraction1",{{"Subtraction", "Elapsed Time2"}}),
    #"Inserted Subtraction2" = Table.AddColumn(#"Renamed Columns1", "Subtraction", each [Task4] - [Task3], type number),
    #"Renamed Columns2" = Table.RenameColumns(#"Inserted Subtraction2",{{"Subtraction", "Elapsed Time3"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns2",{"Product", "Task1", "Task2", "Task3", "Task4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Elapsed Time1", Int64.Type}, {"Elapsed Time2", Int64.Type}, {"Elapsed Time3", Int64.Type}})
in
    #"Changed Type1"

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Inserted Date Subtraction" = Table.AddColumn(Source, "Elapsed Time1", each Duration.Days([Task2] - [Task1]), Int64.Type),
    #"Inserted Date Subtraction1" = Table.AddColumn(#"Inserted Date Subtraction", "Elapsed Time2", each Duration.Days([Task3] - [Task2]), Int64.Type),
    #"Inserted Date Subtraction2" = Table.AddColumn(#"Inserted Date Subtraction1", "Elapsed Time3", each Duration.Days([Task4] - [Task3]), Int64.Type),
    #"Removed Other Columns" = Table.SelectColumns(#"Inserted Date Subtraction2",{"Elapsed Time3", "Elapsed Time2", "Elapsed Time1", "Customer"})
in
    #"Removed Other Columns"

 

[svacharya]

Thank you very much!!! The second solution worked!!!

 

[alansidman]

Thanks for the feedback. Glad to hear it is working for you.