pivot table: how to prepare the data model where located in different columns

Question

Sorry I really don't know what is the best title for this question.
I have a mass data, look like this.
And the final output should be like this.
As you can see in the column label (Economic Label), it has
Agriculture, Forestry and Fishing (A)
Commerce, Transportation, Hospitality, Information/Communication
Construction (F)
Finance, Insurance, Utilities, Real Estate/Housing
Manufacturing (C)
Manufacturing Excluding Construction (B-E)
Public and Other Education, Health

But my question is, how this output generated?
Because Agriculture, Construction ... etc, they are in the different columns. If I put it in the columns, it will look like this. It shows the number, not the title of the record.

I know if I have a table looks like this

  |             1            |
--------------------------------
A | economic sector (header) |
B | Agriculture              |
C | Construction             |
D | ...                      |

Then I can let all the title of record show properly. But if I do in this way, I cannot get the number.
My pivot table looks blank, something like this image.

My question is, if there are different title of record in different columns. For example, Agriculture, Construction, Manufacturing ... etc. How can I use this data and put it into pivot table's column and still get the correct number?

Answer 1

Suppose you start with something like this:

You place your cursor in the table and use Data>Get & Transform Data>From Table/Range. You'll see this in the Power Query Editor:

Select the sector columns, then use Tranform>Unpivot columns.

It will look like this:

Now use Home>Close & Load to put the query back on the spreadsheet.

Create a pivot table from this new data table.

I hope this will be enough to help you get unstuck.