Forum Discussion
Solved
Need help with day count please
I have a dog shelter and I need to count the days that each kennel is empty. Each row is the duration that a dog is in a kennel. I need the difference between the end date of a dog leaving the ke...
Can I borrow your calendar for a minute? I would like some extra days...
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY69CoQwEIRf5Ugd2J+op+1deza2YhG5gIGAoI2P78agEmEJzMy3k+17RUqr1m7ycgHIwMiFCBRhkhh0or6TXYJb81DWSsB3Tv58sHmi1etIOf7mwxxLCLC6SirA+i4xYnUuHiUuNidFCDIXFa2PX/7epTqiEzQC8gOcxzw4Thp2", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Kennel = _t, Dog = _t, #"Start Date" = _t, #"End Date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}},"en-GB"), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Start Date", Order.Descending}}), #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Kennel"}, {{"Rows", each _, type table [Kennel=nullable text, Dog=nullable text, Start Date=nullable date, #"End Date "=nullable date]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Kennel Empty Days", each Int64.From(if Table.RowCount([Rows])>1 then [Rows]{0}[Start Date] - [Rows]{1}[End Date] else Date.From(DateTime.LocalNow()) - [Rows]{0}[End Date]),Int64.Type), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Rows"}) in #"Removed Columns"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.
Can I borrow your calendar for a minute? I would like some extra days...
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY69CoQwEIRf5Ugd2J+op+1deza2YhG5gIGAoI2P78agEmEJzMy3k+17RUqr1m7ycgHIwMiFCBRhkhh0or6TXYJb81DWSsB3Tv58sHmi1etIOf7mwxxLCLC6SirA+i4xYnUuHiUuNidFCDIXFa2PX/7epTqiEzQC8gOcxzw4Thp2", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Kennel = _t, Dog = _t, #"Start Date" = _t, #"End Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}},"en-GB"),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Start Date", Order.Descending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Kennel"}, {{"Rows", each _, type table [Kennel=nullable text, Dog=nullable text, Start Date=nullable date, #"End Date "=nullable date]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Kennel Empty Days", each Int64.From(if Table.RowCount([Rows])>1 then [Rows]{0}[Start Date] - [Rows]{1}[End Date] else Date.From(DateTime.LocalNow()) - [Rows]{0}[End Date]),Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Rows"})
in
#"Removed Columns"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.