Forum Discussion
one row to many rows
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
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.
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
Thanks Tom, did the trick.