Data modelling challenge?

[NickOnTheNet]

Hi all!

I want to produce a report with a chart/charts  comparing revenue by year and month.

The data I have is in the form as below. (It also contains columns for Cost of Goods, Qty Sold and Qty Purchased in the same format as revenue which I have excluded here for brevity.)

Each product has 1 row per year, Rev01-Rev12 are January to December.

Year	Product	RevTotal	Rev01	Rev02	Rev03	Rev04	Rev05	Rev06	Rev07	Rev08	Rev09	Rev10	Rev11	Rev12
2020	AB1	5300,00	750,00	250,00	500,00	500,00		300,00	200,00	100,00	400,00	600,00	700,00	1000,00

I would have prefered having Year, Month, Product, Revenue as the columns, that would have made it easier to my mind  (I'm no expert!)

Could someone suggest either:

1. A method to use the data in the current form

or

2. If it does need to be changed to Year, Month, Product, Revenue as the columns, how I should best go about that?

A pointer to a resource where I could learn would also be much appreciated.

Thanks for your time and consideration.

Regards.. Nick

[FrankAT · Accepted solution]

Hi @NickOnTheNet 

first you have to unpivot the data columns Rev01 - Rev12 in Power Query.

Please provide some more sample data.

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[NickOnTheNet]

Hi @FrankAT ,

Thanks for your input!

I just looked through YouTube and found a number of videos covering the subject, I just needed to know what to look for.

This one covered it perfectly: https://www.youtube.com/watch?v=T8Ekr_sLP-0

I think I've got it from here.

Every day's a school day 😀

Kind regards ... Nick