Forum Discussion
one row to many rows
Hi all, not sure if the subject line is the best to describe what i'm after but figured it would be a starting point.
I've got a table that has one row for a particular ID. For that ID i have caluculated the following, the average amount of hours, the number of months that the hours are to be worked.
So ID 1 has 500 hours over 5 months and the average would be 100 hours per month.
I need to visulize it so that I can see across the x axis 5 months (doesn't need to be months can just be numbers 1 - 5) and then for each of those months 100 hours.
I then intend to roll up all of the other ID's to get a stacked column, or perhaps other visuals, showing the combined totals for each month.
So if I had:
ID 2 with 100 hours over 2 months
ID 3 with 150 hours over 6 months
I'd be able to see in the end something similar to this, but obviously in a visual such as a stacked column etc.
The problem I have is that I only have one row per ID, if I was summing it up from a monthly dataset it would be easy to use the built in quick calculations etc.
I'm stuck in that i'm not sure if I should be trying to create a calculated table which effectively would create one row per number of months for an ID with the avg in each month or something else.
| ID 3 | 25 | 25 | 25 | 25 | 25 | 25 |
| ID 2 | 50 | 50 | ||||
| ID 1 | 100 | 100 | 100 | 100 | 100 | |
| 1 | 2 | 3 | 4 | 5 | 6 | |
| 175 | 175 | 125 | 125 | 125 | 25 |
Any thoughts or help would be appreciated.
Hey,
I'm sorry for the late response.
Here is my solution for your requirement.
Starting with some sample data that looks like this:
In Powerquery I add a custom column using this simple formula:
{1..[no of months]}The curly braces in M the Powerquery language enclose/define a list, the list that will be dfined here starts with the integer value 1 and ends with value from the [no of months] the 2 dots .. indicate that a range will be created.
After applying the formual above, the result should look like this:
Now the function "Expand to new rows" can be used and the "final result" will look like this:
Hopefully this is what you are looking for
Regards
Tom
4 Replies
- TomMartens
Super User
Hey,
I would go with this approach:
Convert the columns that are containing the measurements for the different days into rows, so instead of having x-number of columns where is x equals to: id column plus no of months columns = 1 + 6 = 7 you would come up with just 3 columns
id, month number, hours
Put the month number column to the xaxis, the id to legend and hours to value.
Regards
Tom
Hi Tom, thanks for that reply.. actually that's what i have right now. So:
ID Avg hours #months
1 100 5
2 50 2
3 300 6
Etc.
But what i need for an output is what I have in the original question.
- TomMartens
Hey,
I'm sorry for the late response.
Here is my solution for your requirement.
Starting with some sample data that looks like this:
In Powerquery I add a custom column using this simple formula:
{1..[no of months]}The curly braces in M the Powerquery language enclose/define a list, the list that will be dfined here starts with the integer value 1 and ends with value from the [no of months] the 2 dots .. indicate that a range will be created.
After applying the formual above, the result should look like this:
Now the function "Expand to new rows" can be used and the "final result" will look like this:
Hopefully this is what you are looking for
Regards
Tom
