Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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:
You can merge queries as new.
then just expand out the average column
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.
In this case the Merge + expand action suggested gives me:
or if I expand all the columns then:
What I actually need to achieve is
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:
Perfect, thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |