Forum Discussion
Add year column
Hi Power BI experts :-)
How do I allocate existing data to a year? Do I create a new column in the Excel spreadsheet? Or the Query Editor?
I've created a dashboard from an Excel spreadsheet of data.
Here is the dashboard report:
Here is the query editor view:
And here is the original spreadsheet:
This data was all for calendar year 2016.
I'd like to make a time trend graph on the dashboard for multiple years. I will need to enter data for other years.
But first I need to allocate the data above to the 2016 year.
Should I create a new column in the Excel spreadsheet title Year and fill each row with '2016'?
Or can I create a new column in the Query Editor?
Or is there some other way?
Thanks :-)
This question was solved by Ashish. The solution can be found here:
11 Replies
I guess the best way is to add year column in excel sheet, although you can still create column in powerbi and assing year value to it but you have to put the logic which year value to assign to each row, so coming from excel will make it easy.
Thanks for your response parry2k. Would I have to:
- add a column for 'Year'
- then connect Power BI to the modified Excel spreadsheet
- then run 'Edit Query' and run through all my 'Applied Steps' again?
Or is there a quicker way? I've got five years of data that I must add the year to (2012 - 2016).
Should I keep the data for different years in separate spreadsheets or merge them all into one?
Here are all the steps I applied to transform the data (right side of screen):
no you don't need to change anything in your power query steps, just make sure the step where you use removed columns that year (new column) is not removed.
Basically you can go to each step to make sure year column is part of model, no need to change anything.
Hi,
in your excel you have the column year, so you need charge this column in power bi.
Anonymousare you ok to send your excel and pbix t if it doesn't contain sensitive data. Just put it on google drive and share the link, i will send you the transformed pbix.
This question was solved by Ashish. The solution can be found here:
