Weird! innner join Dataverse tables returns blank and expand returns rows

[Anonymous]

I am checking my queries. It seems they are not working as I expected.

Usually, if an inner join returns blank, it will also return blank after expanding columns. However, when I connect to Dataverse to do this, power query expands columns and rows. Not blank!

Here is the scenario. The two tables are both from Dataverse, where one of them has extra ids. Anyway, I just filtered the first table by those extra ids, and as a result, power query returned blank because these ids didn't exist in this table. After I made an inner join and expanded columns, boom -- those ids came up again!

I tried on tables that come from SharePoint, I didn't find this circumstance. So my concern is whether data sources from Dataverse behave defferently? or there might be an issue? Thanks.

[Syndicate_Admin]

+1 here 

[RobertPaegelow]

It is possible that the behavior you are seeing is related to the way that Power BI handles null values in inner joins when the data is coming from Dataverse. Inner joins in Power BI are designed to return only rows where the values in the joined columns are matched. If there are no matching values in the joined columns, the inner join will return an empty result.

However, when the data is coming from Dataverse, Power BI may treat null values differently than it does for other data sources. This can cause the inner join to return rows that contain null values in the joined columns, even though those values do not match. This can result in the expanded columns and rows that you are seeing in your query.

To avoid this behavior, you can try using a different type of join in your query, such as a left outer join or a full outer join. These types of joins will return all rows from one table, even if there are no matching values in the other table. This can help to ensure that your query returns the expected results, even when there are null values in the joined columns.

[Anonymous]

I am further curious about why would this happen.  Any documents? Thanks a million.