Dates › Duration.Days43 / 47

Progress is saved on this device. Sign in or sign up free to keep it in your account.

MediumFreeDuration.Days

Duration.Days: nights stayed and guest bills

Intan runs a small guest house in Yogyakarta. The booking data only records the check-in date, check-out date and the rate per night. She wants columns for the number of nights and the total bill for each guest.

Task

Add two columns: Malam (nights) holding the number of days in CheckOut minus CheckIn, extracted with Duration.Days (type Int64.Type), then Tagihan (bill) holding [Malam] * [TarifPerMalam]. Result: 6 columns Tamu, CheckIn, CheckOut, TarifPerMalam, Malam, Tagihan and 6 rows in the source order.

Data sourcesBooking

Required functionsDuration.Days

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Run previewCtrlEnter
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
Subtracting two dates in M gives a duration value, not a number. It looks like 3.00:00:00.
Hint 2
Duration.Days(duration) extracts the number of days from a duration as a number, so it can be multiplied by the rate.
Hint 3
#"Added Nights" = Table.AddColumn(Source, "Malam", each Duration.Days(___ - ___), Int64.Type), then add the Tagihan column.

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

let
    Source = Booking,
    #"Added Nights" = Table.AddColumn(Source, "Malam", each Duration.Days([CheckOut] - [CheckIn]), Int64.Type),
    #"Added Bill" = Table.AddColumn(#"Added Nights", "Tagihan", each [Malam] * [TarifPerMalam], type number)
in
    #"Added Bill"

Explanation

[CheckOut] - [CheckIn] gives a duration, for example 3 days. Duration.Days turns it into the number 3 so it can be multiplied by TarifPerMalam. A stay across a month change (29 July to 2 August) is still counted correctly as 4 nights, because M uses the real calendar difference.
Common mistake: multiplying the duration directly by the rate. The result is not rupiah but a very long duration. The subtraction order matters too: [CheckIn] - [CheckOut] gives a negative number. For differences that can be fractional days (for example with check-out times), use Duration.TotalDays.

Power Query· sources: Booking

let
    Source = Booking
in
    Source

Loading the Advanced Editor…

Edit the M code until the result matches, then press Check answer.

Validation result