Split rows by month

[NocturnalSec]

Hi, I'm new to power BI and I'm wondering if there's a way for me to split one row into two in power query.

I have this table called "house booking records", and two columns called "checkindate" and "checkoutdate".

We have done limitation on the datasource for the users to input order no more than 31 days, but we have some orders like below picture, the checkindate and checkoudate are in different month.

So what I want to achieve is when the table sees a row with different month, then it automatically splits or duplicates this row into two: The first row keeps the original checkindate and updates its checkoutdate to end-of-month of the original checkindate. And the second one keeps the original checkoutdate and updates its checkindate to start-of-month of the original checkoutdate. (Both row has to keep all the other original information like room number etc. Only changes the checkin or checkout dates)

Because I didn't find a way to do this in power query, I have to manually update the datasource for every order like this, it's really a large workload as I have more and more orders coming everyday.

So I'm wondering if there's a way to do this.

I'd appreciate any help, thank you in advance!

this is the original state

this is what I want to achieve. 

[ryan_mayu]

@NocturnalSec 

you can try below steps.

1. create a new column

={Number.From([checkindate])..Number.From([checkoutdate])}

2. expand the list(new column)

3. change custom column to date type

4. insert start of month

5. group by

6. remove the useless columns

pls see the attachment below

[NocturnalSec]

This solution didn't work for me, somehow it removed all the orders with same time period, but still I have learned much. So thank you anyway! 🙂

[PawarNovil · Accepted solution]

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.

[NocturnalSec]

This solution worked! Thank you so much!