cancel
Showing results for
Did you mean:
Frequent Visitor

## 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.

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: 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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
4 REPLIES 4
Super User

## Re: one row to many rows

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Highlighted
Frequent Visitor

## Re: one row to many rows

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.

Super User

## Re: 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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Frequent Visitor

## Re: one row to many rows

Thanks Tom, did the trick.