How to pivot this set of data correctly?

I have an excel sheet like this:

|  Time   |  Network1  |  Network2  |  ...  |  Networkn  |
|---------|------------|------------|-------|------------|
|  00:00  |  23.424    |   12323    |  ...  |  412       |
|  00:05  |  3112      |   1322     |  ...  |  123       |
|  ...    |  ...       |   ...      |  ...  |  ...       |
|  23:55  |  1321      |   4124     |  ...  |  214       |

where the values of each network column is the traffic for that specific time.

I want to re-arrange the data so it will create something like this:

|  Time   |  Networks  |  Traffic   |
|---------|------------|------------|
|  00:00  |  Network1  |   23.424   |  
|  00:00  |  Network2  |   12323    |  
|  ...    |  ...       |   ...      |  
|  23:55  |  Networkn  |   214      |

It's like groupby(Time, network_names, traffic_of_each_network_per_Time).

I was playing with the PivotTable Fields but didn't figure it out yet.

Any ideas?

Lev

Posted 2019-05-14T09:53:16.957

Reputation: 143

looks like you need to unpivot the data, you can do this with Power Pivot – PeterH – 2019-05-14T10:14:43.527

Hey @PeterH thanks for commenting. I am afraid I can't get access to the Power Pivot Add-In to check how I can do this. can you think of any other work-arounds? – Lev – 2019-05-14T10:31:00.973

use Power Query to unpivot your data. Since Excel 2016 it's fully integrated in Excel (known as Get & Transform)

– visu-l – 2019-06-04T05:58:31.020

How to pivot this set of data correctly?

No answers