Horizontal to vertical

Zubair

Board Regular
Joined
Jul 4, 2009
Messages
161
Office Version
  1. 2016
Platform
  1. Windows
I have month wise volumes of customer by product horizontally I want data to convert it to vertical please help.

ProductCustomerJanFebMar
Product 1Customer 1200300400
Product 2Customer 1100800900
Product 2Customer 2500850910
Product 2Customer 2600830920
Product 3Customer 1700820930


Result

ProductCustomerMonthVolume
Product 1Customer 1Jan200
Product 1Customer 1Feb300
Product 1Customer 1Mar400
Product 2Customer 1Jan100
Product 2Customer 1Feb800
Product 2Customer 1Mar900
Product 2Customer 2Jan500
Product 2Customer 2Feb850
Product 2Customer 2Mar910
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,475
Office Version
  1. 365
Platform
  1. Windows
Unpivot your data in Power Query

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Product", "Customer"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Book3
ABCD
2Product 1Customer 1Jan200
3Product 1Customer 1Feb300
4Product 1Customer 1Mar400
5Product 2Customer 1Jan100
6Product 2Customer 1Feb800
7Product 2Customer 1Mar900
8Product 2Customer 2Jan500
9Product 2Customer 2Feb850
10Product 2Customer 2Mar910
11Product 2Customer 2Jan600
12Product 2Customer 2Feb830
13Product 2Customer 2Mar920
14Product 3Customer 1Jan700
15Product 3Customer 1Feb820
16Product 3Customer 1Mar930
Table1
 
Solution

Zubair

Board Regular
Joined
Jul 4, 2009
Messages
161
Office Version
  1. 2016
Platform
  1. Windows
Unpivot your data in Power Query

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Product", "Customer"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Book3
ABCD
2Product 1Customer 1Jan200
3Product 1Customer 1Feb300
4Product 1Customer 1Mar400
5Product 2Customer 1Jan100
6Product 2Customer 1Feb800
7Product 2Customer 1Mar900
8Product 2Customer 2Jan500
9Product 2Customer 2Feb850
10Product 2Customer 2Mar910
11Product 2Customer 2Jan600
12Product 2Customer 2Feb830
13Product 2Customer 2Mar920
14Product 3Customer 1Jan700
15Product 3Customer 1Feb820
16Product 3Customer 1Mar930
Table1
Power Query new to me, please let me know the steps to perform till I get the result you shown.
 

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
568
Office Version
  1. 365
Platform
  1. Windows
Unpivot Other Columns in PQ
 

Attachments

  • 11.gif
    11.gif
    199.7 KB · Views: 10

Zubair

Board Regular
Joined
Jul 4, 2009
Messages
161
Office Version
  1. 2016
Platform
  1. Windows
Unpivot your data in Power Query

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Product", "Customer"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Book3
ABCD
2Product 1Customer 1Jan200
3Product 1Customer 1Feb300
4Product 1Customer 1Mar400
5Product 2Customer 1Jan100
6Product 2Customer 1Feb800
7Product 2Customer 1Mar900
8Product 2Customer 2Jan500
9Product 2Customer 2Feb850
10Product 2Customer 2Mar910
11Product 2Customer 2Jan600
12Product 2Customer 2Feb830
13Product 2Customer 2Mar920
14Product 3Customer 1Jan700
15Product 3Customer 1Feb820
16Product 3Customer 1Mar930
Table1
Thanks its working
you are welcome:giggle: power query is a very useful tool.
Yes and with Superman its become more easy to use!
 

Forum statistics

Threads
1,141,474
Messages
5,706,597
Members
421,459
Latest member
drewber

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top