Convert a single column to multiple table columns and create null values

hi BA_Pete

I have manage to solve my data issue - and this now allows me to now create the box & whisker plots in Excel 365 that I could straightforwardly created in Excel 2016.

I needed to modify the tables I appened together to create the data table I originally posted, by adding a "key" column.

My steps:

1) Create individual table of data for each series:  Structure of Table as:

1a) Formula for key column is =COUNTIF($BL$18:BL18,BL18) where column BL is the "Hire" Column

2) Add table to power query

3) Add customer column called "Series Name", with value =specific series name (e.g. List)

4) Repeat for above steps for each required series and change customer column "Series Name" value to suit specific series (e.g. All or Ordered, or Rejected etc).

5) Add new query by combining exisitng query and append data.  This create this data table, slightly modified from original post by the addition of the new "Key" column:

The addition of the "Key" column now allow for the table to split and then re-organised aligning all the "Key" 1s together, then 2s etc etc.

6) Merge columns ("Hire" and "Key" and pick a separator symbol, which is not in either "Hire" column values or or "Key" column values)

7) Pivot "Series Name" column, with "Value Column" set to the "Price" column and under Advanced options select "Don't Aggregate"

😎 Sort table by "Hire" column and then "Key" column, buffered table to ensure all rows are correctly sorted.  Formula as  

= Table.Buffer(Table.Sort(#"Pivoted Column",{{"Hire", Order.Ascending},{"Key", Order.Ascending}}))

9) Remove "Key" and "Merged" column.

10) Final table look like this:

11) Close and Load query to new sheet in excel.