Display non-existent row in Matrix

[Anonymous]

Hello folks,

I have a table with 3 columns "Order", Account" and "Value" as following in Power Queries :

Order	Account	Value
1	A01	1 000,00 €
2	B01	1 500,00 €

In the Matrix chart, I selected as "Order" Show all (1 & 2) and same for the "Account" (A01 & B01), so PBI displayed the entire table that I have in P Queries (It makes sense huh)

My issue is that I want to display all the possible intersections between Order and Account when there is no values linked to those rows. In other words, I need to display a table like this one:

Order	Account	Value
1	A01	1 000,00 €
1	B01
2	A01
2	B02	2 000,00 €

Can you help ?

("The show items with no data" function in PBI Desktop doesn't work here)

Thanks

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

For your issue, do it like this:

My original data table:

//sheet1(3): the second original table; sheet1(4): the third
Table = CROSSJOIN('Sheet1 (3)', 'Sheet1 (4)')

//sheet1: the first original table
Column = 
LOOKUPVALUE(
    Sheet1[Value],
    Sheet1[Order], 'Table'[Order],
    Sheet1[Account], 'Table'[Account]
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.