Transforming a data range into separate rows using Power Query

Problem

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.

 

Source Data

Below is the “SeatBookings” table.

Capture

 

Table with all seats numbers

You will need a table with all the seat numbers in sequence. Let’s call this the “SeatNumbers” table.

Capture

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.

Capture

Capture

8. Expand the SeatNumbers ‘Table’ by clicking on the icon in upper right hand corner of the SeatNumbers column.

Capture

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.

Capture

11. Select the Customer, Seat Start and Seat End columns and select Fill Down option in Transform tab.

Capture

12. Add a Conditional column as below.

Capture

This column will be useful to identify seats that are not booked (Empty).

Capture

 

Visualisation

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.

Capture

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.

 

Final Output

Capture

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.

Capture

 

Credits: This blog was inspired by a solution suggested by Jessica_Seiya here to a question posted by me in Power BI forum.

Advertisements

One thought on “Transforming a data range into separate rows using Power Query

Add yours

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.

Up ↑

%d bloggers like this: