2

I have an Excel document that looks somewhat like this:

Type Amount Days without Days with little Days with lots
Tolerable 5 148 271 206
Less tolerable 4 295 162 258
A little intolerable 9 195 234 159
Intolerable 13 277 133 115

I'd like to put Type and Amount into a pie chart. Furthermore I want to split each pie section into its other 3 columns, Days without, Days with little and Days with lots.

Sunburst seemed like a good choice to me. However for it to work I need to restructure it into some hierarchical table.

Result Input Factor
Tolerable Days with 740 148 5
Days with little 1355 271
Days without 1030 206
Less tolerable Days with 1180 295 4
Days with little 648 162
Days without 1032 258
A little intolerable Days with 1755 195 9
Days with little 2106 234
Days without 1431 159
Intolerable Days with 3601 277 13
Days with little 1729 133
Days without 1495 115

This gives me the result I'd expect:

expected sunburst chart

I have a large dataset however and converting it all into some hierarchical data seems way too inefficient to me. Is there an easier method to get this done?

1 Answer 1

4

Reformatting data for a sunburst chart is a bit awkward, but here's a way to cut down some of the tedious steps.

First, use Data>Get & Transform Data>From Table/Range.

This will open a query against your source data:

enter image description here

Select the three right-most columns and use Transform>Unpivot Columns, so you get this:

enter image description here

Use Add Column>Custom Column, configured like this:

enter image description here

So you get this:

Use Home>Close & Load To, like this:

enter image description here

Configure the pivot table like this:

enter image description here

If your row headers have repeated values in each row of the 'Type' column, use Design>Report Layout>Do not repeat item labels so the pivot table looks like above.

Now select the entire pivot table and copy it. Paste Special as Values in some other sheet. Use the pasted result as the source of your sunburst chart.

enter image description here

2
  • Great walkthrough! I can't seem to find the "Get Data & Transform" option unfortunately - is it something that's not available on Excel 365 for MacOS yet? Or do I need to install an extension? Jan 27 at 21:52
  • From what I can tell, the 'From Table/Range' data source is not available for Power Query on Mac. You can read more here: techcommunity.microsoft.com/t5/excel-blog/… Jan 27 at 22:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.