Forum Discussion
Solved
CalculateTable - Create rows with two columns and their distinct values
Hello,
I have a table like this:
| Person | Week Number | Value |
A | 1 | 100 |
| A | 2 | 50 |
| A | 3 | 20 |
| B | 1 | 50 |
| B | 3 | 10 |
| C | 1 | 15 |
| C | 2 | 55 |
What I want is to take the unique values for Person and the unique values for Date and make a row for each unique pair, to create a new table like this:
| Person | Week Number |
| A | 1 |
| A | 2 |
| A | 3 |
| B | 1 |
| B | 2 |
| B | 3 |
| C | 1 |
| C | 2 |
| C | 3 |
That way, where for example person B doesn't have an entry for week 2, I can add a lookup column on the new table and say the value is 0.
Is there a formula to create this new table? I am thinking union, product, summarize.... not sure!
Erf19 - Try:
Table = GENERATE( DISTINCT('Table'[Person]), DISTINCT('Table'[Week]) )
