Fiscal year sum to columns

G

Gohard

New member
Joined
Mar 27, 2019
Messages
1
Reaction score
0
Points
0
Excel Version(s)
365 ProPlus
Hello,
I am trying to create a query where data regarding fiscal years for the same customer will be visible in columns rather than rows. Here is example of kind of data I have:

CountryCustomerFiscal yearSales
Austria12345620185980
Austria1234562019598
Germany98745201852222
Spain65452018486
UK256120184
Austria5845220188321
Austria5420192
Germany948201931


And to be able to make Year over year calculation I would like to see this data in columns (example below):

CountryCustomer20182019 YoY
Austria1234565980598 2019/2018
Austria123456212 2019/2018
Germany98745 2019/2018


Is it possible to do it within the query? I am just starting and am still struggling a bit. Example also attached.
View attachment example.xlsx
 
Highlight your table. Data..from Table/Range ... with [x] my table has headers. In powerquery click on the fiscal year column and do transform....pivot... For value column pick sales. Not sure the point of the final column YOY. If you want the delta, then click on both the 2018 and 2019 columns and right click replace and then replace null with 0. Then Add Column..Custom column... and use formula [2019]-[2018] and click ok

Code: 
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Customer", Int64.Type}, {"Fiscal year", Int64.Type}, {"Sales", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Fiscal year", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Fiscal year", type text}}, "en-US")[#"Fiscal year"]), "Fiscal year", "Sales", List.Sum),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"2018", "2019"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "YOY", each [2019]-[2018])
in
    #"Added Custom"
 
Last edited:
You must log in or register to reply here.
Back
Top