Pivot Year Please Help

[netanel]

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!

[netanel]

Solution please anyone?

[Fowmy · Accepted solution]

@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

[netanel]

Thank you so so mach! Greet Guy!

[Fowmy]

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]

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?

[Fowmy]

@netanel 

I have attached the file for you to learn and understand:
https://1drv.ms/x/s!AmoScH5srsIYgYFctF_wc5uOXEjf8g?e=eYJEpI

This was done in Power Query which is available in Power BI and Excel 2016+Above. Refer some videos to learn more.
https://www.youtube.com/channel/UCKwBEguA8IlBubIobaOormg

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

 

[Fowmy]

@netanel

The 1st link works, I can mail you the file if do not mind sending me the email to my PM.
 
 

[netanel]

the first link dont work.

and link number two is greet but dont do the work for me....

Tanks

[Mariusz]

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]

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!