Power Query Row Filter Not Applying to Joined Table?

[knotty]

Hi All

I'm having an issue with applying row filters in Power Query to dataset A and the results coming through in dataset B:

How do I prevent the 10,000 showing in the above data table?  Based off my understanding of table/query joins, if dataset A is joined to dataset B via a left outer join on Customer No. (A to B), then the 10,000 shouldn't be there.  I understand I could just apply a page or visual filter to exlcude the blanks from dataset A but I'd like to understand why the above is happenning first and if there's something basic I'm doing incorrectly.  I've tried everything I can think of, including amending the join type in the model every which way possible and merging the two queries in Power Query, none of it seems to work.

Any help or advice would be greatly appreciated.

Thanks!

[wdx223_Daniel]

delete the relationship between these two table. and try this measure

CountOfCustomer=CALCULATE(DISTINCTCOUNT(TableB[Cusotmer]),TREATAS(VALUES(TableA[Customer]),TableB[Customer))

[v-cgao-msft]

Hi @knotty ,

This is not related to PowerQuery. This is the row that the engine automatically adds when the relationship is invalid.

When two tables are linked through a regular relationship, the table on the one-side might contain the additional blank row in case the relationship is invalid. Thus, if the many-side of a regular relationship contains values that are not present in the table on the one-side, then a blank row is appended to the one-side table.

So even if you check the table, you won't see this empty row because it was created automatically during the loading of the data model. At some point, if the relationship becomes valid at a later time (all the customer No.'s in table B can be corresponded to in table A), then the blank row will disappear from the report.

Please see the phenomenon described in this blog.

Blank row in DAX

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[knotty]

Hi Gao

Thanks very much for your response, very helpful!

I've watched the video and understand that there are DAX measures I can use to include or exclude the records summarised within the blank row in DAX.

What I'm struggling to get my head around though is the fact that we can't easily omit these records through table linkage or merging the queries in Power Query.  An inner or left outer join, by definition, should exclude these blank results (it even says so when using merge queries in Power Query).  The fact they're not seems a fundamental concept of data analysis to be neglected, and I can't think of any benefit it'd ever bring.

The example above is probably the most simple and visible occurrence of this issue but, what happens when there's a dataset joining 100 tables?  Are we expected to just know that blank matches will be included, even though we've specifically asked for them not be when merging queries?  I thought a fundamental design advantage of Power BI was to empower data analysts to publish datasets to inexperienced users so that they may self-serve their own BI requirements – how should they be expected to know to use specific, advanced DAX measures to get correct data? 

Without the basic functionality of table/query joining, I don’t feel confident in being able to publish accurate info.  Why does merging queries in Power Query just not work?  How is this not a more widely discussed issue – is it just me failing to understand some new best practices?

Thanks!