Power Query: Seating Plans

Welcome to our Power Query blog. This week, I look at an example where I need to calculate the stock needed from a supplier.

The example I am looking at today combines some Power Query functionality in order to find the solution with a supply problem. I will start by looking at the data below, which shows me the chairs hired in the last month and the amount that the supplier who maintains the chairs, delivered to my hire company.

The question is: did I have the correct number of chairs supplied? I know that each set has eight chairs in it, but I can’t tell if I have the right number by looking at this data.

I will start by creating a query for my order data, by choosing ‘From Table’ in the ‘Get & Transform’ section on the ‘Data’ tab.

I start by to creating a new column in my query which will show all the quantities in chairs: I will use a ‘Custom Column’ for this on the ‘Add Column’ tab.

The M formula I am using is 

I now have all my quantities in chairs, but my supplier only deals in sets, so I need to know how many sets I need of each type. I need to divide the chair quantity by eight and then round up. I could do this in another custom column, but this time I will use the options in the ‘Standard’ dropdown on the ‘From Number’ section of the menus instead. I can’t use the ‘Divide (Integer)’ option because this will round down, and I need to round up. Instead I use two steps, starting with ‘Divide’.

I choose to divide by eight (8).

I then use the ‘Rounding’ dropdown to choose to ‘Round Up’.

I can then rename my new column and remove Division.

Now I have my quantity in sets, I can group my data.

I need to group by chair type, and for each chair type I need to sum the number of sets.

I have my total number of sets for each type, so now I can ‘Close & Load To’ from the ‘Home’ or ‘File’ tab to compare this with the number delivered.

I specify where I would like the data to appear on my sheet.

Clearly, I haven’t had enough budget chairs, so I’ve been paying extra as 11 sets must have come from the standard or luxury chairs! I can now alter my future orders to allow for this.

Come back next time for more ways to use Power Query!