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 with the topic

Thank you!

 

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

10 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi netanel 

     

    Is do you need this as Matrix visual only or are you looking to change the underlying data structure?

    Only asking as the current data structure is best practice and easier to handle with DAX.

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

    • netanel's avatar
      netanel
      Post Prodigy
      6 years ago

      hey Mariusz

       

      I need that also for matrix visual and also for zebra Table Visual and for moor table.

      But i dont whant to change my Data the Only change that i need its inside powerbi.

       

      Thanx for the quick respons!

       

       

  • 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!

    • Fowmy's avatar
      Fowmy
      Super User
      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