Horizontal to vertical

[Zubair]

I have month wise volumes of customer by product horizontally I want data to convert it to vertical please help.

Product	Customer	Jan	Feb	Mar
Product 1	Customer 1	200	300	400
Product 2	Customer 1	100	800	900
Product 2	Customer 2	500	850	910
Product 2	Customer 2	600	830	920
Product 3	Customer 1	700	820	930

Result

Product	Customer	Month	Volume
Product 1	Customer 1	Jan	200
Product 1	Customer 1	Feb	300
Product 1	Customer 1	Mar	400
Product 2	Customer 1	Jan	100
Product 2	Customer 1	Feb	800
Product 2	Customer 1	Mar	900
Product 2	Customer 2	Jan	500
Product 2	Customer 2	Feb	850
Product 2	Customer 2	Mar	910

 

[alansidman]

Unpivot your data in Power Query

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

Book3
	A	B	C	D
2	Product 1	Customer 1	Jan	200
3	Product 1	Customer 1	Feb	300
4	Product 1	Customer 1	Mar	400
5	Product 2	Customer 1	Jan	100
6	Product 2	Customer 1	Feb	800
7	Product 2	Customer 1	Mar	900
8	Product 2	Customer 2	Jan	500
9	Product 2	Customer 2	Feb	850
10	Product 2	Customer 2	Mar	910
11	Product 2	Customer 2	Jan	600
12	Product 2	Customer 2	Feb	830
13	Product 2	Customer 2	Mar	920
14	Product 3	Customer 1	Jan	700
15	Product 3	Customer 1	Feb	820
16	Product 3	Customer 1	Mar	930
Table1

 

[Zubair]

Unpivot your data in Power Query

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

Book3
	A	B	C	D
2	Product 1	Customer 1	Jan	200
3	Product 1	Customer 1	Feb	300
4	Product 1	Customer 1	Mar	400
5	Product 2	Customer 1	Jan	100
6	Product 2	Customer 1	Feb	800
7	Product 2	Customer 1	Mar	900
8	Product 2	Customer 2	Jan	500
9	Product 2	Customer 2	Feb	850
10	Product 2	Customer 2	Mar	910
11	Product 2	Customer 2	Jan	600
12	Product 2	Customer 2	Feb	830
13	Product 2	Customer 2	Mar	920
14	Product 3	Customer 1	Jan	700
15	Product 3	Customer 1	Feb	820
16	Product 3	Customer 1	Mar	930
Table1

Power Query new to me, please let me know the steps to perform till I get the result you shown.

 

[shaowu459]

Unpivot Other Columns in PQ

 

[Zubair]

Unpivot Other Columns in PQ

Thanks very good and quick

 

[shaowu459]

Thanks very good and quick

you are welcome power query is a very useful tool.

 

[Zubair]

Unpivot your data in Power Query

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

Book3
	A	B	C	D
2	Product 1	Customer 1	Jan	200
3	Product 1	Customer 1	Feb	300
4	Product 1	Customer 1	Mar	400
5	Product 2	Customer 1	Jan	100
6	Product 2	Customer 1	Feb	800
7	Product 2	Customer 1	Mar	900
8	Product 2	Customer 2	Jan	500
9	Product 2	Customer 2	Feb	850
10	Product 2	Customer 2	Mar	910
11	Product 2	Customer 2	Jan	600
12	Product 2	Customer 2	Feb	830
13	Product 2	Customer 2	Mar	920
14	Product 3	Customer 1	Jan	700
15	Product 3	Customer 1	Feb	820
16	Product 3	Customer 1	Mar	930
Table1

Thanks its working

you are welcome power query is a very useful tool.

Yes and with Superman its become more easy to use!