cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
celebrindal Frequent Visitor
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.

 

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 3252525252525
ID 25050    
ID 1100100100100100 
 123456
       
 17517512512512525

 

Any thoughts or help would be appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
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:

image.png

 

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.

image.png

 

After applying the formual above, the result should look like this:

image.png

 

Now the function "Expand to new rows" can be used and the "final result" will look like this:

image.png

 

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
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!
celebrindal Frequent Visitor
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
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:

image.png

 

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.

image.png

 

After applying the formual above, the result should look like this:

image.png

 

Now the function "Expand to new rows" can be used and the "final result" will look like this:

image.png

 

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!
celebrindal Frequent Visitor
Frequent Visitor

Re: one row to many rows

Thanks Tom, did the trick.