Forum Discussion
Source Data has all dates as column headers
I have a spreadsheet as the source data where KPIs are rows and report date is the column headers.
I'm fairly new to power bi but it seems I'll have a lot more success in creating new measures and visualizations for historic comparisons if I can reformat the data to have a date column.
So... Q1. is this assumption correct? or should I go back to more basic tutorials on creating measures.
Q2. How to I fix this data?
I can adjust the source tables but they need to remain very easily usable for the entry level staff that inputs data - also its accessed and updated by multiple different people. Dramatically changing the format will cause confusion, errors and they'll be frustrated with me and less helpful.
Here is the format I expect is the best practice
This is the current source format. (It goes back for years and there ar 80 more rows.) (the file doesn't seem to be embedding so here is a screenshot)
Q1. Your assumption is correct. Much easier to work with date data as rows.
Q2. Because there are numerous areas the date is entered (seemingly at every 'Category' of data) we will need to filter then unpivot your data.
To start, I'm assuming your data is coming into Power BI similar to this:
We will promote headers then filter out other date junk in the same column.Now the table should look like this
Select the first column (mine is membership) and Transform > Unpivot Columns > Unpivot Other Columns
Rename a few columns and you're done!
2 Replies
Q1. Your assumption is correct. Much easier to work with date data as rows.
Q2. Because there are numerous areas the date is entered (seemingly at every 'Category' of data) we will need to filter then unpivot your data.
To start, I'm assuming your data is coming into Power BI similar to this:
We will promote headers then filter out other date junk in the same column.Now the table should look like this
Select the first column (mine is membership) and Transform > Unpivot Columns > Unpivot Other Columns
Rename a few columns and you're done!
I wish I asked before googling, watching way too many youtube tutorials and just experimenting.
That worked exactly as I hope and was simpler than I tried to make it. Thank you!
