Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
Solved! Go to Solution.
This question was solved by Ashish. The solution can be found here:
This question was solved by Ashish. The solution can be found here:
Hi,
in your excel you have the column year, so you need charge this column in power bi.
How do i do that Daniel?
Please explain like I'm 5 (ELI5).
Thanks.
@powerbi-learnerare 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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thanks @parry2kparry2k. You're a legend!!
I have a lot of trouble transforming my Excel spreadsheets in the Query Editor so that each variable has its own column.
I'm having trouble getting my head around Transpose, Pivot, Unpivot etc
With the new column 'Year' added I just can't seem to get the columns set up correctly in the Query Editor like they were when Year wasn't present.
And having trouble getting my Excel sheet in the right format.
Thanks.
Here is the Google Drive link containing the pbix file and the Excel spreadsheet. Data is in the tab "All Data":
https://drive.google.com/drive/folders/1n6JbptK6smXQOJazEViXIepAkrl7obxD?usp=sharing
Hi, I am new in this topic, but I found a solution.
please review the file shared.
https://drive.google.com/file/d/18HbGmC1Z-hi1M6Xzn8gWuY2Tl-UpBpr2/view?usp=sharing
@DanielRodriguez thanks for your effort. It's not quite what I'm looking for.
My column headings should be:
Year, DHB, Population, Enrolled, Base Population
Please see the Edit Query screenshot above.
I had a nice flat table in PBI until I added the year column in order to make a time trend line graph.
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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thanks for your response parry2k. Would I have to:
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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
I've added a year column, along with data from other years.
Do you have any advice on how I'd transform the data to make it organised like it was before? With the Year column in place I'm just getting tangled up when I try to transform the data into the nice table I had when there was just one year of data.
Thanks so much. Here's how the data looked when there was just one year. I assumed that I just needed to add another column with the year:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |