Forum Discussion
Solved
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 B...
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.
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: