Calculate days between dates on 2 different rows

[TeisL]

Hi All,

I'm looking for a way to calculate the number of days between 2 dates on 2 different rows:

I have values that end on BD and end on OD. I want the number of days between the MAX date of BD and the MIN date of OD, based on the same value.

In example below I want to calculate the number of days between 

If no BD-value was found, it should be blank.

Any idea's?

[wdx223_Daniel]

NewStep=Table.Combine(Table.Group(PreviousStepName,"WKN AARDCONT",{"n",each let a=List.Max([Stop BD Date]),b=List.Min(List.RemoveNulls([Start OD Date])) in Table.AddColumn(_,"Days",each if a=null then null else Duration.Days(a-b))},1,(x,y)=>Value.Compare(Text.Start(x,7),Text.Start(y,7)))[n])

[Anonymous · Accepted solution]

Hi @TeisL ,

In summary, the steps are: Split columns-->Groupby -->Add Custom -->Expand and remove,

Below is the whole M syntax:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdBNCoBACAXgu8y6ePrsZ9xG+w4Q3f8aWVBMNAUuhA986romlaFd5tQkdRf0IKNPW/OQ3kEUkNvpgCiKEEqY1sj+aIDJLWcQRUdkUMukSyyWqwH1GyK9JtJBy3P8ujPHtvAXxCw7HjCesu0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WKN = _t, #"Start OD Date" = _t, #"Stop BD Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WKN", type text}, {"Start OD Date", type date}, {"Stop BD Date", type date}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "WKN", "WKN - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "WKN - Copy", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"WKN - Copy.1", "WKN - Copy.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"WKN - Copy.1", Int64.Type}, {"WKN - Copy.2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"WKN - Copy.1"}, {{"Count", each _, type table [WKN=nullable text, Start OD Date=nullable date, Stop BD Date=nullable date, #"WKN - Copy.1"=nullable number, #"WKN - Copy.2"=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [
max=Record.Field( Table.Max([Count] ,"Stop BD Date"),"Stop BD Date"),
min=Record.Field(Table.Min(Table.SelectRows([Count],each [Start OD Date] <>null ),"Start OD Date"),"Start OD Date"),
diff=Duration.Days(max-min) 
][diff]),
    #"Expanded Count" = Table.ExpandTableColumn(#"Added Custom", "Count", {"WKN", "Start OD Date", "Stop BD Date"}, {"WKN", "Start OD Date", "Stop BD Date"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Count",{"WKN - Copy.1"})
in
    #"Removed Columns"

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vijay_A_Verma]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY5BDsAgCAT/wlkSWbRJ32L8/zdKjTUWSbgwwLCtkVAiZIBzZcAa6mmnF0M+ikXF6k/lHgZ1u7ZYbOCovt6DBt4lAaOcVFiqo4UFrOK+jfPp1TCvhnk1zKtb3mnoDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"WKN AARDCONT" = _t, #"Start OD Date" = _t, #"Stop BD Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WKN AARDCONT", Int64.Type}, {"Start OD Date", type date}, {"Stop BD Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"WKN AARDCONT"}, {{"Max BD Date", each List.Max([Stop BD Date]), type nullable date}, {"Min OD Date", each List.Min([Start OD Date]), type nullable date}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Diff", each if [Max BD Date]=null then null else Duration.TotalDays([Max BD Date]-[Min OD Date])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Max BD Date", "Min OD Date"})
in
    #"Removed Columns"

[TeisL]

Hi @Vijay_A_Verma ,
Thank you for your response.

Unfortunately, I don't think this is the correct solution yet. This is the result: