Calculate per month in a same row

[wadidaw]

I need to calculate days per month >>(departure - arrive) +1, but in the table show different month in the same row

I have table like this

And this is the table result i want to have.

Any suggestions are very useful for me

Thanks.

[AlienSx]

@wadidaw why last row days is 16?

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    days = (from as date, to as date) => List.Generate(
        () => [f = from, t = List.Min({to, Date.EndOfMonth(f)})], 
        (x) => x[f] <= to, 
        (x) => [f = Date.AddDays(x[t], 1), t = List.Min({to, Date.EndOfMonth(f)})],
        (x) => {x[f], x[t], Duration.Days(x[t] - x[f]) + 1}
    ), 
    to_list = Table.ToList(
        Source, 
        (x) => days(Date.From(x{0}), Date.From(x{1}))
    ), 
    tbl = Table.FromRows(List.Combine(to_list), Table.ColumnNames(Source) & {"Days"})
in
    tbl

[jennratten]

Hello @wadidaw - this is how you can accomplish this calculation.  Note, the results for the bolded items are the same; the results for the other cells are a little different but the math is correct based on the formula you provided in the description. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstQ31DcyMDJR0gEyjSHMWB2QuBFC3MgcRcIcSYcBQsbQAGEWkG2KImOKJGMIk4oFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Arrive = _t, Depart = _t]),
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Depart", type date}, {"Arrive", type date}}),
    #"Inserted Date Subtraction" = Table.AddColumn(#"Changed Type1", "Subtraction", each Duration.Days([Depart] - [Arrive]) + 1, Int64.Type)
in
    #"Inserted Date Subtraction"

[wadidaw]

Hi @jennratten Thanks for your reply, i need to have additional row every each different month period to add 1st day of month (Arrive) and last day of month (depart). I need to calculate per month

[jennratten]

You most recent description of what you are trying to achieve seems very different from the expected result originally posted.  Can you please clarify what it is you are trying to accomplish?  Are you trying to add a new column which returns the number of days between the arrival and departure date - or are you trying to add rows for some sort of monthly calculation?  Perhaps you could reply with another example of the expected result.

Thanks!