Forum Discussion
Generating rows of data based on count in column
I have data on two different granularity levels:
1. One result per one row, for example
Category Result Cat1 1 Cat1 3 Cat1 5 Cat2 3 Cat2 4
2. Data for previous periods, where the detail has been lost, for the same data it would look like this
Category Count Average Cat1 3 3 Cat2 2 3.5
To make an interactive report where all the data is included I would prefer to generate dummy rows from the aggregated data, that is
Category Result Cat1 3 Cat1 3 Cat1 3 Cat2 3.5 Cat2 3.5
Preferrably this step should take place in Power Query, so that I could append the two datasets. Any ideas on how to proceed?
I see. So back in your second table, you can add a custom column with the following code:
{ 1..[Count]}That will produce a list starting at 1 to whatever the # in count is. You can go ahead and expand that out
Append with table one, and you get this table
Only interested in Category, Result and Average, so can remove all the other columns.
Then just merge the Result and Average columns and you the following table:
4 Replies
I see. So back in your second table, you can add a custom column with the following code:
{ 1..[Count]}That will produce a list starting at 1 to whatever the # in count is. You can go ahead and expand that out
Append with table one, and you get this table
Only interested in Category, Result and Average, so can remove all the other columns.
Then just merge the Result and Average columns and you the following table: