none
Insert Rows based in criteria/conditions #2 RRS feed

  • Question

  • Hello,

    After the precious help received from Colin, I am facing a similar problem (insert rows with criteria) with a another table:

    Now the problem is a little bit different, but in very similar situation

    In my case I have the following initial table:

    PS.: Here in Brazil our date format is dd.mm.yy

    IdGuest GuestName NumContract  NumRes   Checkin  Checkout  StatusRes  UsedPoints RoomNights

    1         JOSE LIMA          1-5        1309560   12/12/14  14/12/14  Checkout      5000           2

    2         CARLOS SILVA     2-6        1309552   15/12/14  18/12/14  Checkout      6000           3

    3         JORGE DIAS        3-1         1309501   18/12/14  18/12/14  Checkout       3000         1

    My goal is through PowerQuery create the following Table

    As you can note I need to create new rows from each reservation according the number of RoomNights.

    IdGuest GuestName NumContract  NumRes   Checkin  Checkout  StatusRes  UsedPoints RoomNights

    1         JOSE LIMA          1-5        1309560   12/12/14  14/12/14  Checkout      5000           2

    1         JOSE LIMA          1-5        1309560   12/12/14  14/12/14  Checkout      5000           2

    2         CARLOS SILVA     2-6        1309552   15/12/14  18/12/14  Checkout      6000           3

    2         CARLOS SILVA     2-6        1309552   15/12/14  18/12/14  Checkout      6000           3

    2         CARLOS SILVA     2-6        1309552   15/12/14  18/12/14  Checkout      6000           3

    3         JORGE DIAS        3-1         1309501   18/12/14  18/12/14  Checkout      3000          1

    Besides that two more collumns has to be added:
    PointsByDay = >Total Points divided by Room Nights
    UsedDay = > This is the Date of each night stayed.

    For example: If the period of the reservation was from 12.12.14 until 14.12.14, the first record of UsedDay would be 12.12.14 (the first night) and the second record would be 13.12.14(second night).

    Please note that in the Third reservation the customer even did not sleep, it was a day use. In this case the UsedDay would be equal to the CheckinDate

    I would appreciate any help
    Best Regard
    Jose Ricardo



    • Edited by ricafonyat Sunday, October 8, 2017 4:33 PM Better Formatting
    Sunday, October 8, 2017 4:28 PM

Answers

  • The solution is similar to the one that I provided in your previous thread.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"IdGuest", Int64.Type}, {"GuestName", type text}, {"NumContract", type text}, {"NumRes", type text}, {"Checkin", type date}, {"Checkout", type date}, {"StatusRes", type text}, {"UsedPoints", Int64.Type}, {"RoomNights", Int64.Type}}),
        AddedCustom = Table.AddColumn(ChangedType, "Custom", each List.Repeat({[Checkin]}, [RoomNights])),
        ExpandedCustom = Table.ExpandListColumn(AddedCustom, "Custom"),
        RemovedAddedCustom = Table.RemoveColumns(ExpandedCustom,{"Custom"}),
        GroupedRows = Table.Group(RemovedAddedCustom, {"IdGuest"}, {{"Table", each _, type table}}),
        AddedIndex = Table.TransformColumns(GroupedRows, {"Table", each Table.AddIndexColumn(_, "Index", 0, 1)}),
        CombinedTables = Table.Combine(AddedIndex[Table]),
        AddedUsedDay = Table.AddColumn(CombinedTables, "UsedDay", each [Checkin] + #duration([Index], 0, 0, 0)),
        RemovedIndex = Table.RemoveColumns(AddedUsedDay,{"Index"})
    in
        RemovedIndex

    • Marked as answer by ricafonyat Sunday, October 8, 2017 8:35 PM
    Sunday, October 8, 2017 8:22 PM

All replies

  • Hi Jose,

    In your previous thread, could you please indicate whether or not the solution provided answers the question you asked? Your response saves the forum administrator from having to make a determination.

    Thank you.

    "Please note that in the Third reservation the customer even did not sleep, it was a day use. In this case the UsedDay would be equal to the CheckinDate"

    Why then does the table falsely register 1 night instead of 0? Also, how would the UsedDay be different in the case where the customer actually stays 1 night (e.g. checks in 12/12/14 and checks out 13/12/14)?


    Sunday, October 8, 2017 5:42 PM
  • Hi Collin, 

    As I am new in here, I think I did the right thing in my previous thread. I just clicked "mark as answer".

    Did I?

    Thank you!

    Even if the guest stayed for a few hours during the same day that is considered as 1 Room Night.

    So that for our calculation is the same of someone checkedin 12/12/14 and checkedout 13/12/14. Both of them spent one day.

    Best Regards and many thanks for you support.



    Sunday, October 8, 2017 7:50 PM
  • The solution is similar to the one that I provided in your previous thread.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"IdGuest", Int64.Type}, {"GuestName", type text}, {"NumContract", type text}, {"NumRes", type text}, {"Checkin", type date}, {"Checkout", type date}, {"StatusRes", type text}, {"UsedPoints", Int64.Type}, {"RoomNights", Int64.Type}}),
        AddedCustom = Table.AddColumn(ChangedType, "Custom", each List.Repeat({[Checkin]}, [RoomNights])),
        ExpandedCustom = Table.ExpandListColumn(AddedCustom, "Custom"),
        RemovedAddedCustom = Table.RemoveColumns(ExpandedCustom,{"Custom"}),
        GroupedRows = Table.Group(RemovedAddedCustom, {"IdGuest"}, {{"Table", each _, type table}}),
        AddedIndex = Table.TransformColumns(GroupedRows, {"Table", each Table.AddIndexColumn(_, "Index", 0, 1)}),
        CombinedTables = Table.Combine(AddedIndex[Table]),
        AddedUsedDay = Table.AddColumn(CombinedTables, "UsedDay", each [Checkin] + #duration([Index], 0, 0, 0)),
        RemovedIndex = Table.RemoveColumns(AddedUsedDay,{"Index"})
    in
        RemovedIndex

    • Marked as answer by ricafonyat Sunday, October 8, 2017 8:35 PM
    Sunday, October 8, 2017 8:22 PM
  • Collin, fantastic!

    Thanks a lot!

    I owe you couple of beers!

    After reading carefully your code, I started to understand the whole process. 

    Best Regards
    Sunday, October 8, 2017 8:38 PM