Forum Discussion

netanel's avatar
netanel
Post Prodigy
6 years ago
Solved

Pivot Year Please Help

Hello everyone! I have DATA as in the red color image And I want to turn it into the yellow color I tried some formulas and PIVOT and it doesn't work I would love for a simple formula or help wit...
General Comment
  • Fowmy's avatar
    Fowmy
    6 years ago

    netanel 

    You don't need to do any transformation to get the data presented in the layout you wanted. Just add an index column in Power Query and insert a Matrix Visual then drop Year in Column, Index in Row, and Amount in Value, Done.

    Please Check this file: https://1drv.ms/u/s!AmoScH5srsIYgYFhaaJI141dwgVkhg?e=4rap1Z

    Mark this reply as a solution if I answered your question

Fowmy's avatar
Fowmy
Super User
6 years ago

Hi, 

If you want to do it in Power Query, Load your table to Power Query (It's Table3 in my example) having Year and Amount as columns. Go to Advanced Editor clear existing codes and paste the code I shared.

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Amount", "Amount - Copy"),
    #"Added Index" = Table.AddIndexColumn(#"Duplicated Column", "Index", 0, 1),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Index", {{"Year", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Added Index", {{"Year", type text}}, "en-US")[Year]), "Year", "Amount - Copy", List.Sum),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Index", "2017", "2018", "2019", "Amount"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Index"})
in
    #"Removed Columns"

 

 

Did I answer your question? Mark my post as a solution!

netanel's avatar
netanel
Post Prodigy
6 years ago

Hi
Sorry but I didn't understand what to do?
The formula is a bit unclear to me, and where to pot that in Query?