After Cross-join, expanding column filters my records

[PowerFabian]

Hello,

i have a problem in Power BI Query editor. I cross-joined two tables (left-cross-join) and added the value of a parent table like that (see picture below). The column Dim_Customer is the joined table of the related data. By expanding the columns of it, all of my data is filtered for the null values in the column CustomerNo. I know that it can not find a value for it in the related table, but i want to keep my records that contains a null value and write  a null value in the expanded Column.

How can I adjust the power query editor, so it doens't filter for the null values?

Hope someone can help me with that.

Regards,

Fabian

[PhilipTreacy · Accepted solution]

Hi @PowerFabian 

Are you sure that null value is being filtered out?  It's not just being pushed down the column by the expanding table(s)?

In the ReceiptNo column in Row 1, in yor image before the Tables are expanded the number is 332979, and after expansion the number is 332980.  How is that happening?  Same on Row 2, the ReceiptNo changes.

Before you expand the tables, add an Index column, then when you expand the tables that will give you a visual clue as to where the rows have shifted to.

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[PowerFabian]

Hi @PhilipTreacy,

i was quite sure about it, because i had a measure that counts the rows of blank CustomerNo's and an other measure giving me the percentage share of rows without a blank CustomerNo's. Especially the last one was 100% afterwards. 
But your tip with the index helped me indeed. After inserting the index column my records aren't filtered for blank customer no's. When deleting the step (inserting index) after i expanded the crossjoined column, my records are missing again. I really have no clue why this happens but I guess i just keep the index column now as it works this way.

Thank You very much.

Regards,

Fabian

[AlB]

Hi @PowerFabian 

Can you share the pbix? or the M code you are using and the original table you are processing?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[PowerFabian]

Hi,

sorry i cant share the pbix with you because it contains confidential data. But i tried to reproduce my issue with a test excel and it works fine at it. But doing the same with my original pbix still filters for the null values.

Here is what it looks like before:

Than i Do my crossjoining and expanding like that in M Language:

And this is my result: (After crossjoining, still looks fine)

 and after expanding my null value is missing: (I have a measure that counts for the null values and it's blank afterwards as well.

Hope you can help me like that aswell.

Thank you!

Regards,

Fabian