Webbo3 Power BI · Cohort 1 · Pod Project · Released Day 14 · Presented Day 16

Pod Project: Jade Hotels & Resorts

Five hotels, six booking channels, a year of bookings, and a client with two questions your pod answers together, in front of her, on Day 16.

By the end of today you can

Run the whole pipeline as a team: connect, clean, append, merge, model, measure, visualise
Answer the client's festive season question with fair comparisons
Break down what each booking channel really costs in commission
Split the work by seat so every member owns a part
Prepare a ten-minute presentation for Day 16

The Solo Project proved you can build alone. This one proves you can build with other people, which is how every real report is made: one person cleans, another models, another measures, and somebody has to stand up and explain it.

The Brief

Pod Project · Team · Released Day 14 · Presented Day 16

Mrs Folake Adeyemi owns Jade Hotels & Resorts: three city hotels and two resorts. She has two questions. First: "Did the festive season actually make us more money, or does it just feel busy?" Second: "The booking sites bring guests, but how much of our money do they keep in commission, and which channel is worth it?" Build one Power BI report that answers both, and present it to her.

Your tutor plays Mrs Adeyemi on Day 16. She is friendly, busy, and not a data person: she wants answers in naira and in plain words.

The Dataset

Jade Hotels & Resorts

Download all as one zip file (jade-hotels.zip)

Bookings_Jul-Dec_2024.csv : 1,161 bookings
Bookings_Jan-Jun_2025.csv : 1,091 bookings
Hotels.csv : the five hotels: city, type and number of rooms
Channels.csv : the six booking channels and the commission rate on each

What we are looking atWe want to see the first rows of the July to December file exactly as it arrives.

Bookings_Jul-Dec_2024.csv, as it arrives

	ABCBookingID	ABCBookingDate	ABCHotelID	ABCChannelID	ABCGuestType	ABCRoom Type	ABCCheckIn	ABCCheckOut	ABCNightly Rate	ABCStatus
1	JH24-00001	31/05/2024	H1	C6	Leisure	STANDARD	01/07/2024	06/07/2024	95000	confirmed
2	JH24-00002	18/06/2024	H1	C3	Leisure	STANDARD	01/07/2024	04/07/2024	95000	confirmed
3	JH24-00003	09/06/2024	H2	C6	Business	suite	01/07/2024	05/07/2024	240000	Confirmed
4	JH24-00004	11/06/2024	H3	C3	Business	Deluxe	01/07/2024	02/07/2024	105000	confirmed
5	JH24-00005	30/06/2024	H4	C2	Leisure	·Standard	01/07/2024	06/07/2024	80000	Confirmed
6	JH24-00006	25/05/2024	H1	C3	Business	Deluxe	02/07/2024	05/07/2024	145000	Confirmed
7	JH24-00007	23/05/2024	H1	C6	Business	standard	02/07/2024	04/07/2024	95000	confirmed
8	JH24-00008	24/06/2024	H2	C3	Business	Standard	02/07/2024	09/07/2024	85000	No-Show

10 COLUMNS, 1,161 ROWS · first 8 shown

What we are looking atWe want to see Hotels.csv, one of the two lookup tables.

Hotels.csv

	ABCHotelID	ABCHotelName	ABCCity	ABCHotelType	ABCRooms
1	H1	Jade Victoria Island	Lagos	City Hotel	120
2	H2	Jade Maitama	Abuja	City Hotel	95
3	H3	Jade Port Harcourt	Port Harcourt	City Hotel	70
4	H4	Jade Tinapa Resort	Calabar	Resort	60
5	H5	Jade Obudu Mountain Resort	Obudu	Resort	45

5 COLUMNS, 5 ROWS

What we are looking atWe want to see Channels.csv, one of the two lookup tables.

Channels.csv

	ABCChannelID	ABCChannel	ABCCommissionRate
1	C1	Walk-in	0.0
2	C2	Jade Website	0.0
3	C3	Booking.com	0.15
4	C4	Expedia	0.18
5	C5	Travel Agent	0.1
6	C6	Corporate Contract	0.05

3 COLUMNS, 6 ROWS

Column	What it holds
BookingID	The booking's unique reference
BookingDate	When the guest booked, as text day/month/year
HotelID	Which hotel. Matches Hotels[HotelID]
ChannelID	How they booked. Matches Channels[ChannelID]
GuestType	Leisure or Business
Room Type	Standard, Deluxe or Suite
CheckIn, CheckOut	Arrival and departure, as text day/month/year
Nightly Rate	The price per night, in naira
Status	Confirmed, Cancelled or No Show. Only Confirmed bookings earned money

What Is Wrong With It

Problem	Hint
Two half-year files	Append into one query called Bookings
Dates are text, day first	Change Type, Using Locale, Date, English (United Kingdom) (Day 2)
Room Type in 12 spellings	Trim, then Capitalize Each Word
Status in 9 spellings: CONFIRMED, Canceled, Cancelled, Confirmed, No Show, No-Show, cancelled, confirmed, no show	Trim, Capitalize Each Word, then Replace Values: Canceled to Cancelled, No-Show to No Show
No number of nights	Add a custom column, Nights (below)

Nights is the number of days between check-in and check-out. Power Query works it out with a button: select the later date first, then the earlier one, and subtract.

Click path

1.Select CheckOut › Ctrl+click CheckIn › Add Column › Date › Subtract Days

2.Rename the new column Nights › Data Type: Whole Number

What we are checkingWe want a Nights column, typed as Whole Number, worked out for every booking.

Power Query preview: Bookings

	ABCBookingID	DATECheckIn	DATECheckOut	1²3Nights
1	JH24-00001	01/07/2024	06/07/2024	5
2	JH24-00002	01/07/2024	04/07/2024	3
3	JH24-00003	01/07/2024	05/07/2024	4
4	JH24-00004	01/07/2024	02/07/2024	1
5	JH24-00005	01/07/2024	06/07/2024	5

4 COLUMNS, 2,252 ROWS · first 5 shown

Check the Sign

Nights must be positive. If every value comes out negative, the two columns were subtracted the other way round: delete the step, select them in the opposite order, and do it again.

Who Does What: Roles by Seat

Your pod has ten seats. Two people share each role, so nobody is ever the only one who knows a part. Everybody builds in their own .pbix; the pair leads merge the finished steps into the pod file.

Seats	Role	Owns
1 and 2	Data	Connect, clean, append, Nights, merges by ID
3 and 4	Model	Date table with its Festive column, relationships, star shape, the Model view screenshot
5 and 6	Measures	Every measure below, named, formatted, in _Measures, checked against the self-check
7 and 8	Report	The three report pages: layout, visuals, slicers, titles
9 and 10	Story	The findings, the slides or talking points, and leading the Day 16 presentation

Everyone Presents Something

On Day 16 every member speaks for at least thirty seconds about the part they built. Mrs Adeyemi may ask any of you any question, so learn what your pod-mates did.

What Your Pod Must Build

#	Task	Done when
1	Append and clean both files as above	Bookings has 2,252 rows; Status has 3 values
2	Add the Nights column (above)	Nights on every row, as a Whole Number
3	Merge Hotels and Channels into Bookings by ID only	No blank HotelName or Channel
4	Date table for 1 July 2024 to 30 June 2025, marked, with a Festive column (below)	365 rows, marked
5	Relate Bookings[CheckIn] to Date[Date], and Bookings to Hotels and Channels	Three Many-to-one, Single relationships
6	The measures below	Every card matches the self-check
7	Page 1 Overview, Page 2 Festive Season, Page 3 Channels	Three tidy pages
8	A ten-minute presentation	Rehearsed once, timed

The relationship is on CheckIn: a booking belongs to the day the guest arrived. That is why the Date table stops at 30 June 2025, even though a few June guests leave in July.

The festive season is a property of the day, so it lives on the Date table as a calculated column. Every page then splits by 'Date'[Festive], and a measure can count the days in each season with COUNTROWS ( 'Date' ).

What this code doesWe want every day of the Date table labelled Festive or Rest of year, so any measure can be split by season.

New column

// Festive season as the client defines it: 15 December to 7 January
Festive =
IF (
    ( MONTH ( 'Date'[Date] ) = 12 && DAY ( 'Date'[Date] ) >= 15 )
        || ( MONTH ( 'Date'[Date] ) = 1 && DAY ( 'Date'[Date] ) <= 7 ),
    "Festive",
    "Rest of year"
)

Count of rows in Date, by Festive

Festive days

341

Rest of year days

&& means AND and || means OR, the same as the words in Power Query.

The Measures

Measure	What it answers	Hint
Bookings	How many bookings of any status?	COUNTROWS
Room Nights	How many nights did guests actually stay?	CALCULATE, SUM of Nights, Confirmed only
Room Revenue	How much did the rooms earn?	CALCULATE and SUMX of Nights times Nightly Rate, Confirmed only
Commission	How much did the channels keep?	SUMX over Confirmed bookings of the revenue times `RELATED ( Channels[CommissionRate] )`
Net Revenue	What did Jade keep?	Room Revenue minus Commission
Cancellation Rate	What share of bookings were cancelled?	CALCULATE, COUNTROWS, DIVIDE
Average Nightly Rate	What did a night cost on average?	DIVIDE Room Revenue by Room Nights

What we are checkingWe want the self-check: the overview cards with no filters. Your Measures pair must match every one.

Self-check: overview cards

2,252

Bookings

5,427

Room Nights

₦647.0M

Room Revenue

₦646,956,000

₦48.9M

Commission

₦48,922,660

₦598.0M

Net Revenue

₦598,033,340

11.5%

Cancellation Rate

₦119,211

Average Nightly Rate

Question 1: Did the Festive Season Pay?

The trap is comparing totals. The festive season is 24 days and the rest of the year is 341 days, so of course the rest of the year earns more in total. A fair comparison divides by the number of days, or looks at the price per night.

What we are checkingWe want the self-check for the festive page: revenue in total, per day of the season, and per night sold.

Self-check: by season

Season	Days	Room Revenue	Revenue per Day	Average Nightly Rate
Festive	24	₦77,551,000	₦3,231,292	₦137,991
Rest of year	341	₦569,405,000	₦1,669,809	₦117,041
Total	365	₦646,956,000	₦1,772,482	₦119,211

What we are checkingWe want the same per-day comparison split by hotel type, because the client runs two kinds of business.

Self-check: per day by hotel type

Hotel type	Revenue per Day, Festive	Revenue per Day, Rest of year	Festive is
City Hotel	₦1,761,375	₦1,216,334	1.4x
Resort	₦1,469,917	₦453,475	3.2x

"Revenue per Day" is DIVIDE ( [Room Revenue], COUNTROWS ( 'Date' ) ): the days in the current filter come from the Date table, so with Festive on the rows it divides by the days of each season. Your Story pair decides what these numbers mean for Mrs Adeyemi.

Question 2: What Do the Channels Really Cost?

What we are checkingWe want the self-check for the channels page: revenue, commission and what Jade keeps, for each channel, most commission first.

Self-check: by channel

Channel	Rate	Room Revenue	Commission	Net Revenue
Booking.com	15%	₦145,097,000	₦21,764,550	₦123,332,450
Expedia	18%	₦60,917,000	₦10,965,060	₦49,951,940
Travel Agent	10%	₦91,745,000	₦9,174,500	₦82,570,500
Corporate Contract	5%	₦140,371,000	₦7,018,550	₦133,352,450
Jade Website	0%	₦145,577,000	₦0	₦145,577,000
Walk-in	0%	₦63,249,000	₦0	₦63,249,000
Total		₦646,956,000	₦48,922,660	₦598,033,340

A channel with a high rate is not automatically bad: it may bring guests nobody else would. The fair questions are how much revenue each channel brings, how much it keeps, and whether the direct channels (Walk-in and the Jade Website, which cost nothing) could grow.

Day 16: The Presentation

Ten minutes, then five minutes of questions from Mrs Adeyemi.
Open the report live in Power BI Desktop. Slides are optional; the report is not.
Answer her two questions in the first two minutes, then show how you know.
Every member speaks. Day 16's lesson has the full structure.

What to Hand In, by the Start of Day 16

The pod's .pbix (PodName_JadeHotels.pbix) as a Google Drive link, "Anyone with the link can view".
A Model view screenshot and a screenshot of each of the three pages.
A one-page Google Doc: who sat in which seat and did what, and the two answers in plain words.

How It Is Marked

Criterion	Marks	What the tutor checks
Pipeline	20	Appended, cleaned, Nights and Festive right, merges by ID
Model	15	Date table marked, three relationships, star shape
Measures	20	All seven correct and formatted; the self-check matches
Festive analysis	15	A fair comparison (per day or per night), by hotel type, clearly shown
Channel analysis	15	Commission and net revenue by channel, with a recommendation
Presentation	15	Answers first, every member speaks, questions handled from the report

The pod receives one mark. Your tutor may move an individual's mark up or down if the roles document and the presentation show they did much more or much less than their seat.

Questions You Will Have

Does a cancelled booking count in Room Revenue?

No. Only Confirmed bookings earned money. Cancelled and No Show bookings still count in Bookings and in the Cancellation Rate.

Should the festive season follow the booking date or the check-in date?

The check-in date. Mrs Adeyemi means guests who stayed over the holidays, not guests who booked then.

Two of us want to build the same thing. What now?

Go back to the seat table. The pair that owns it decides, and the other pair checks it.

Tick Before You Move On

☐ Bookings has 2,252 rows and Status has three values
☐ Nights is on every booking; Festive is on the Date table; merges are by ID
☐ The overview cards match the self-check
☐ The festive comparison is per day or per night, not totals
☐ Every member knows what they will say on Day 16

Quick Recap

One team, one report, two client questions. Clean, add Nights, merge by ID, label festive days on the Date table, measure. Compare seasons fairly: per day or per night, not totals. Commission is revenue times the channel rate; net is what Jade keeps. Ten minutes on Day 16, answers first, everyone speaks.

Final Scenario

On Day 16 Mrs Adeyemi sits down and asks, "So, did Christmas pay?"

Your Story lead answers in one sentence with a naira figure per day, and points at the page. Your Data lead explains how a festive booking was defined. Your Measures lead shows the commission table. She leaves knowing which booking site to renegotiate.

That is a team delivering, not a group of people who happened to share a file.

Next, Day 15: slicers, drill-through and dashboard storytelling, exactly what your Report and Story pairs need.