List Month end or end date between date ranges

[RK9009]

Hi All, 

I am working two columns: 
Example,
StudentID                Start Date     End Date
1                                4/25/2021    7/15/2021    
2                                2/5/2021.    5/15/2021

I am trying generate a list of dates that include month end dates or end date. An example of the result that I am looking for,

StudentID                Start Date     End Date.             New Column 
1                                4/25/2021    7/15/2021           4/30/2021
1                                4/25/2021    7/15/2021           5/31/2021
1                                4/25/2021    7/15/2021           6/30/2021
1                                4/25/2021    7/15/2021           7/15/2021 - this is the end date 
2                                2/5/2021.    5/25/2021            2/28/2021 
2                                2/5/2021.    5/25/2021            3/31/2021 
2                                2/5/2021.    5/25/2021            4/30/2021 
2                                2/5/2021.    5/25/2021            5/25/2021


Any thoughts on how to do this. Thank you so much.

@amitchandak 

            

[tackytechtom · Accepted solution]

Hi @RK9009,

I think I have a solution for you problem:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIy1TfRNzIwArENTfXNIexYnWglI6CIqb4RTBKo0BQqGQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StudentID = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StudentID", Int64.Type}, {"Start Date", type date}, {"End Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each { Number.From([Start Date])..Number.From([End Date]) }),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each Date.EndOfMonth([Custom])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each if [Custom.1] = [Custom] or [End Date] = [Custom] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each ([Custom.2] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom.1", "Custom.2"})
in
    #"Removed Columns"

Hope this helps!

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom

[RK9009]

Thank you. Tom, this is perfect. 

[RK9009]

@jennratten