There are instances where a data range needs to be transformed into rows.
e.g. Concert hall seat bookings. A customer may book a range of seats like from 1 to 5.
If you want to display the booked seats then you will need to have one row per seat with respective customer name who has booked the seat.
To learn how to solve this problem using DAX check the blog Transforming a data range into separate rows using Power Query.
Below is the “SeatBookings” table.
Table with all seats numbers
You will need a table with all the seat numbers in sequence. Let’s call this the “SeatNumbers” table.
Solution in M / Power Query
1. Load the SeatBookings and SeatNumbers table into Power BI.
2. Go to Advance Query editor.
3. Select the SeatBookings table.
4. Select Combine -> Merge Queries -> Merge Query as New.
5. Select Start Seat in the merge window.
6. Select SeatNumbers for the second table and select SeatNum field.
7. Then select Full Outer as Merge Kind.
8. Expand the SeatNumbers ‘Table’ by clicking on the icon in upper right hand corner of the SeatNumbers column.
If you notice the first 5 rows are not in sequence. This where the next step is very critical for this solution.
9. Sort the SeatNumbers.SeatNum column in ascending order.
11. Select the Customer, Seat Start and Seat End columns and select Fill Down option in Transform tab.
12. Add a Conditional column as below.
This column will be useful to identify seats that are not booked (Empty).
I used Card visuals and added the Booked Customer in the Fields. I added SeatNumbers.SeatNum into Visual Filter and set the advance filter as below.
Copy the Card visual and paste as many visual as needed depending on how many eats you have. Change the Visual Filter value to respective seat number. e.g. Card filter for seat number 2 will have the Visual filter value ‘Show items when the value:’ “is” 2.
If you use a slicer to display bookings by Customer please note that all other Card visuals for other customers will display “Blank” as value. So if you need to see a customer booking along with empty seats then you will need to Ctrl click the Customer and ‘Empty’ in the slicer.
Credits: This blog was inspired by a solution suggested by Jessica_Seiya here to a question posted by me in Power BI forum.