Filter and transpose data from rows to column

P

Promaster

New member
Joined
Jan 28, 2017
Messages
2
Reaction score
0
Points
0
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 codeYearSalesCost of good soldService expensesothers expensesSalaries and wages
346049320131796831776154400784042177
346049320141618991313120403926663068
346049320152123680157694917763872
320136544047240152616537302850662019813
320146300933287123313060092598102011480
320078065786366277826449082354061335212
320088398515365530324343832750121428076
32009413120488034911232462906701284175
320107007606329256915285772919751746040
320117699536326515819189003219532030972
320126989641289445419715053431312018046
832013816723515025776017388165071

final output would be like this:

Company codeSales 2014Sales 2013Sales 2012Cost of good sold 2014Cost of good sold 2013Cost of good sold 2013Service expenses 2014Service expenses 2013Service expenses 2012others expenses 2014others expenses 2013others expenses 2012Salaries and wages 2014Salaries and wages 2013Salaries and wages 2012
3460493
3
83

Thanks.
 
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,
 

Attachments

  • Promaster.xlsx
    20.8 KB · Views: 45
You must log in or register to reply here.
Back
Top