How to unpivot a tables with multiple column

Occasional Visitor

Dear all

I have to solve a problem of date calculation


Example, guests stay in a hotel from 30 Sep 2021 - 7 October 2021.
I want to know how many "night"that guest stayed in Sep and Oct separately


I managed to do the calculation with end of month to get in Power Query

September - 1 night  October - 6 nights

However, I want the end result to display as 


Sep - 1 night

Oct - 6 nights


I tried many ways to unpivot, but unable 


Besides, is it a better way to calculate the nights by month?


Thanks in advance  


Best regards




1 Reply


Depends on how your data is organized. Do you record each day or only check-in/check-out dates?