Convert dataset with monthly data in columns to flat file

[heiastart95]

Hi all!

I have an excel file with values sorted on a monthly basis according to the month names in the columns.
In order to analyse my data in e.g. PowerBI, I want to convert the structure of my data set to a flat file structure, i.e. instead of having my monthly value corresponding to the month columns, I want three new columns (Month, Year and Value). I have attached an extract of my dataset illustrating how it is currently structured, and the result I hope to get to. The full data set I want to transform has values from January 2018 to September 2021 (i.e. 50 columns) and +3000 rows.

Any tips regarding this issue would be highly appreciated!

[williaml]

Welcome. You could use tidyr::pivot_longer()to get the months in to a long format, create columns from that for the month and year.

Pivot data from wide to long — pivot_longer

[heiastart95]

@williaml

Welcome. You could use tidyr::pivot_longer() to get the months in to a long format, create columns from that for the month and year.

Pivot data from wide to long — pivot_longer • tidyr

Thanks - worked perfectly!