Many to many relationship + Power Query Filter

[11097486]

Hello, when I create a many to many relationship with 3 tables, I have a problem when I add a filter in power query.

Relationship

Result 1 without a filter in power query: Everything is normal

Result 2 with a date filter (after 2018) in power query: A blank row appears.

I do not understand de logic or the reason why the blank row appears. Any idea?

Thank you

[parry2k]

@11097486 what you mean by the filter in PQ? It shows blank coz value doesn't exist in the operational table and probably there are transactions in operations products.

Also why you have a cross filter to both between products and operational products table

[11097486]

Hello, 

1) I have a cross-filter because I want to be able to see the product codes linked to the operation codes.

2) I filter in PQ beaucoup I have millions of operation and I don't need to upload data from 2015 when I refresh my report.

Here's a simpler example. I removed 2020-01-01 with a filter in PQ. I just don't understand why it turns to Blank in the matrix. It souldn't since there is no blank dates in PQ, no?

Thank you

[v-janeyg-msft]

Hi, @11097486 

It’s my pleasure to answer for you.

According to your description, Even if you filter 2020/1/1 of Table 1 in PQ, but the 'operation id' corresponding to the date of 2020/1/1 in Table 2 is still there, then Table 3 will generate three values of AA, BB, and DD according to the filtering relationship, So there is a value even if it is blank.

Like this:

You can directly use a date slicer or filter date in filter pane.

Like this:

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[11097486 · Accepted solution]

Hello, yes I know that I can filter directly with a slicer. I was just wondering if it is possible to filter directly in PQ to avoid loading millions of lines. However, it seems like filtering in PQ only applies to one table and not to the other tables linked to it. Thank you for your help!