I have a table like this:
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:
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!
Go to Solution.
@Erf19 - Try:
View solution in original post
@Erf19 , Try a new Table like
union(addcolumns(except(crossjoin(distinct(Table[Person]),distinct(Table[Week])), selectcolumn(Table,"Person",[Person],"Week",[Week])),"Number Value",0),Table)
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.