Split rows by month

Hi NocturnalSec 
I have tried solving your issue and got the expected result on my end.

Output:

Please copy the below M code and paste it into the new table's Advance editor,  check and follow each and every step closely. 

----------------------------------------------------------------------------------------------------------------------------------------------

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY1LCgAhDEPv0rWCTVt7GPH+15gq+BmY2SXh8dIaQTNzRgEoUSTGKUw9tfdouUxaIiOWAfwrROSTKFgOVZ1EWC+xX4SZTUKWeIwb1yi11u2Q9bLLwN2den8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CheckinDate = _t, CheckOutDate = _t, ID = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CheckinDate", type date}, {"CheckOutDate", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([ID] = "111" or [ID] = "222")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each {Number.From([CheckinDate])..Number.From([CheckOutDate])}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom", "CheckoutDate2"}}),
#"Inserted Month" = Table.AddColumn(#"Renamed Columns", "Month", each Date.Month([CheckinDate]), Int64.Type),
#"Renamed Columns1" = Table.RenameColumns(#"Inserted Month",{{"Month", "CheckInMonth"}}),
#"Inserted Month1" = Table.AddColumn(#"Renamed Columns1", "Month", each Date.Month([CheckoutDate2]), Int64.Type),
#"Renamed Columns2" = Table.RenameColumns(#"Inserted Month1",{{"Month", "CheckoutMonth"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns2", "EOM", each let
eom=Date.EndOfMonth([CheckoutDate2]),
flag= if [CheckoutDate2]=eom then "YES" else "NO"
in flag),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "FilterFlag", each if Number.From([CheckinDate])<= Number.From([CheckOutDate] )and [EOM]= "YES" then "YES" else if [CheckOutDate]=[CheckoutDate2] then "YES"
else "NO"),
#"Filtered Rows1" = Table.SelectRows(#"Added Custom2", each ([FilterFlag] = "YES")),
#"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"ID", "CheckinDate", "CheckOutDate", "CheckoutDate2", "CheckInMonth", "CheckoutMonth", "EOM", "FilterFlag"}),
#"Added Custom3" = Table.AddColumn(#"Reordered Columns", "CheckInDate2", each if Date.Month([CheckinDate])=Date.Month([CheckoutDate2]) then [CheckinDate]

else Date.StartOfMonth([CheckoutDate2])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom3",{"ID", "CheckoutDate2", "CheckInDate2"}),
#"Reordered Columns1" = Table.ReorderColumns(#"Removed Other Columns",{"ID", "CheckInDate2", "CheckoutDate2"})
in
#"Reordered Columns1"

--------------------------------------------------------------------------------------------------------------------

Regards,

Novil

If I answer your question, please mark my post as a solution.