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

[pb296]

hi All

This original post is close but not quite getting me the solution I'm after:

https://community.fabric.microsoft.com/t5/Desktop/Convert-a-single-column-to-multiple-table-columns/...

It almost gets me to the structure i need but each row/column has "error" as the returned value

@Seth_C_Bauer added you in specifically as you provided the solution above.  Hope this is ok?

I have a table in power BI that looks like this:

I'm trying to change the "Series Name" column into individual seperate new columns based on the values in "Series Name" (there are 4 values in this column: "List", "All", "Ordered", "Rejected").

I'm a specifically wanting to retain each individual data point in each series - I'm not wanting to sum or aggreate etc

My issue is, whilst each of the series all have some data for each of the values listed in "Hire" column, each series has a different number of instances in each e.g. 

Series "List" only has 1 data point for each "Hire" category: Tier05...(count=1), Tier06..(count=1), Tier07..(count=1), Tier08...(count=1), Tier09...(count=1), Tier10...(count=1), but

Series "All" has multiple and varying No of data point for all the categories: e.g. Tier05... (count=358), Tier06.. (count=274), Tier07..(count=305), Tier08...(count=928), Tier09...(count=1261), Tier10...(count=1171).

In essence I'm looking for a way that generate "null" value in each of the new series column (List, All, Ordered, Rejected) if there is not sufficient data points to populate all the rows in that specific series column based on the "Hire" column values

The output I'm looking for should look like this (Index column added to help visualise how the tabel changes as the row No increase):

Then further down the table it would look like this.  Between row 2 (above) and row 358 (below) the values for "List" in these rows woudl be "null".

The same principle would apply on series "Ordered" and "Rejected" series but the no of rows and where these rows would be null is more varied.  Series "List" is the worst case senario.

Hope this makes sense?

Really hoping you can help me

Thank you inadvance

Paul

[BA_Pete]

Hi @pb296 ,

This looks like an XY Problem to me.

The key question is why? Why are you trying to get your data into this format? What's your use-case/end goal.

Your data is already in the optimal format for Power BI modelling/calculation, so can you give a bit of detail on the bigger picture please as there may be a faster/easier/more robust way to get to what you're trying to achieve.

Pete

[pb296]

Hi BA_Pete

I'm trying to create some box and whisker plots where I can graphcially compare the series (List, All, Ordered, Rejected) again each other by the Hire categories. (Tier05..., Tier06... etc).

I used to be able to do this very easly in Excel 2016, but in excel 365, I cannot control the x-axis ranges seperately for each series (Hire categoriies range).  I can add to the box and whisker chart each series, but they all have to have the same x-axis range, which they didnt in excel 2016.  Therefore I can no longer align my box & Whisker plots correctly by the Hire categories. (Tier05..., Tier06... etc)

However, if everything is in one table (with null values where needed) I can still generate the charts I need.

[BA_Pete]

Ah, Excel. I thought you were using this in Power BI.

Do you have an example of your required visual output and which columns/values in your desired output would populate each aspect of the B&W chart please? Not 100% sure I can help you to do it in Excel to be honest, but happy to take a look. Are you using/do you use the Excel Data Model at all?

Pete

[pb296 · Accepted solution]

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:

Key	Hire	Price
1	Tier05	100
2	Tier05	101
3	Tier05	124
4	Tier05	76
1	Tier06	90
2	Tier06	122

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.

[BA_Pete]

Hi @pb296 ,

Cool, glad you got it sorted.

As @v-kpoloju-msft said, I also appreciate you taking the time to update the thread with your discovered solution. Really helpful for future readers with the same issue 👍

Pete

[v-kpoloju-msft]

Hi @pb296, 
Thank you for the reply and glad to hear that you have managed to resolve the issue. 

Adding the “Key” column and restructuring your tables in Power Query was an excellent approach this ensures each series aligns correctly for your Box & Whisker plots in Excel 365.

Appreciate your time and effort in updating the thread with your solution. Please accept your solution as an answer, so this will help other community members facing a similar issue.