Forum Discussion
Calculate days between dates on 2 different rows
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?
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.
4 Replies
- Vijay_A_Verma
Most Valuable Professional
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"Hi Vijay_A_Verma ,
Thank you for your response.Unfortunately, I don't think this is the correct solution yet. This is the result:
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.- wdx223_Daniel
Community Champion
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])
