Split rows by days into multiple by dates range

Hi adicarmeli_ ,

In PQ,

This can be done with two custom column in PQ but more is better so you'll  understand what is happening in  each step. The first column is to count how many days are within the start and end dates. The second column to create a list that can be expanded to into rows. And two more columns to get the new start and end dates.

Paste the following in advanced editor in a blank query. Make sure that everythign else is deleted before pasting.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTVNzTUNzIwMlIwNLQysLQyMFDSUTI0RxE1BInGxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type datetime}, {"End Date", type datetime}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Max Number of Rows", each let 
start = Date.From([Start Date]),
end = Date.From([End Date]),
days = Duration.Days(end - start)+1
in days, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "Rows", each {1..[Max Number of Rows]}),
    #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom", "Rows"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Rows", Int64.Type}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Start Date2", each if [Rows] =1 then [Start Date]  else DateTime.From(Date.AddDays( Date.From([Start Date]), [Rows]-1 )), type datetime),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "End Date 2", each if [Rows] = [Max Number of Rows] then [End Date]  else DateTime.From(Number.From(Date.From([Start Date2]))+1-(1/86400)), type date)
in
    #"Added Custom3"