AngusFair
New member
- Joined
- Jul 21, 2016
- Messages
- 3
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- 2013/365
Hello all,
I'm not sure if this is a Power Query question, but as that's the tool I'm trying to use, I'll start here.
I have a project where I need to determine the total number of hotel rooms available for sale by day. This calculation take the total number of rooms in the building, subtracts the number of out-of-order (OOO) rooms and the number of do-not-sell (DNS) rooms, and then gives me a number. I then divide the number of rooms sold by the derived value to determine the actual occupancy %.
The problem is that the table of OOO-DNS rooms doesn't give me a count by date. It gives me the room number and the first and last days it went out of order:
My problem is figuring out how to tell Excel take this data and build a proper data table that would look like the following:
This would solve two problems. First would be that I could do a pivot table of OOO and DNS rooms by day and reason. Second, there are some duplicate entries, so I may have room 2231 going OOO from July 8 to July 15, but also going OOO from July 8 to July 21, etc. I've had a hard time automatically deleting the correct duplicate entry, so once the data is in a proper table format, it would be simple to remove any duplicate dates.
Thank-you. Please let me know if there is any further info I could provide.
AF
I'm not sure if this is a Power Query question, but as that's the tool I'm trying to use, I'll start here.
I have a project where I need to determine the total number of hotel rooms available for sale by day. This calculation take the total number of rooms in the building, subtracts the number of out-of-order (OOO) rooms and the number of do-not-sell (DNS) rooms, and then gives me a number. I then divide the number of rooms sold by the derived value to determine the actual occupancy %.
The problem is that the table of OOO-DNS rooms doesn't give me a count by date. It gives me the room number and the first and last days it went out of order:
| Room Number | Code | First Date | Last Date |
| 2145 | OOO | July 7, 2015 | Aug 4, 2015 |
| 2674 | DNS | June 28, 2015 | July 9, 2015 |
| 2231 | OOO | July 8, 2015 | July 15, 2015 |
My problem is figuring out how to tell Excel take this data and build a proper data table that would look like the following:
| Date | Room Number | Code |
| July 7, 2015 | 2145 | OOO |
| July 8, 2015 | 2145 | OOO |
| July 9, 2015 | 2145 | OOO |
| ... etc. |
This would solve two problems. First would be that I could do a pivot table of OOO and DNS rooms by day and reason. Second, there are some duplicate entries, so I may have room 2231 going OOO from July 8 to July 15, but also going OOO from July 8 to July 21, etc. I've had a hard time automatically deleting the correct duplicate entry, so once the data is in a proper table format, it would be simple to remove any duplicate dates.
Thank-you. Please let me know if there is any further info I could provide.
AF