Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Display non-existent row in Matrix

Hello folks,

 

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

 

OrderAccountValue
1A011 000,00 €
2B011 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:

 

OrderAccountValue
1A011 000,00 €
1B01 
2A01 
2B022 000,00 €

 

Can you help ?

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

 

Thanks

Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    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.

     

1 Reply

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    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.