Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

List Column.png

Append with table one, and you get this table 

Append Table.png

 

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:

Final Table.png

 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

You can merge queries as new.  

Merge Queries as new.png

then just expand out the average column

Expand Out Merge.png

Anonymous
Not applicable

Let me update the question. The categories in the aggregated table might match the ones in the data table, but they might also be different.

data.PNG


aggregated.PNG

 

In this case the Merge + expand action suggested gives me:

merge.PNG

or if I expand all the columns then:

expand.PNG

What I actually need to achieve is
desired.PNG

Anonymous
Not applicable

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

List Column.png

Append with table one, and you get this table 

Append Table.png

 

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:

Final Table.png

 

Anonymous
Not applicable

Perfect, thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.