PDA

View Full Version : Filter and transpose data from rows to column



Promaster
2017-01-28, 10:21 AM
Hi, I'm new to power query and vba.
I have a data file up to 20 000 rows. I have multiple rows for each company according to the year.
I'm looking for a quick way to filter by year and transpose, and have only one row for each company.
Here is a sample of the first rows


Company code
Year
Sales
Cost of good sold
Service expenses
others expenses
Salaries and wages


3460493
2013
179683
1776
154400
7840
42177


3460493
2014
161899
1313
120403
9266
63068


3460493
2015
212368
0
157694
9177
63872


3
2013
6544047
2401526
1653730
285066
2019813


3
2014
6300933
2871233
1306009
259810
2011480


3
2007
8065786
3662778
2644908
235406
1335212


3
2008
8398515
3655303
2434383
275012
1428076


3
2009
4131204
880349
1123246
290670
1284175


3
2010
7007606
3292569
1528577
291975
1746040


3
2011
7699536
3265158
1918900
321953
2030972


3
2012
6989641
2894454
1971505
343131
2018046


83
2013
816723
515025
77601
7388
165071



final output would be like this:



Company code
Sales 2014
Sales 2013
Sales 2012
Cost of good sold 2014
Cost of good sold 2013
Cost of good sold 2013
Service expenses 2014
Service expenses 2013
Service expenses 2012
others expenses 2014
others expenses 2013
others expenses 2012
Salaries and wages 2014
Salaries and wages 2013
Salaries and wages 2012


3460493

















3

















83


















Thanks.

Ken Puls
2017-01-28, 06:36 PM
The actions you need to pull this off are the following:
-Unpivot
-Merge
-Pivot

I've attached a workbook with a sample that does what I believe you're looking for.

Hope this helps,

Promaster
2017-01-29, 10:33 PM
Great Thanks. Very useful...