Convert dataset with monthly data in columns to flat file

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!

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.

Thanks - worked perfectly!

1 Like

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.