Data Model Relationships

[Krash7000]

Hi All,

I'm have created a data model through power query, and I have created relationships to several other tables to look up values. I just can't work out how I would go about finding any 'missing lookups' as such.

If I had merged the queries I could have filtered on the column to find the blanks, however as it's just a relationship I can't work out the way to identify any errors.

Is there a way to do this?

 

[Matt Allington]

I normally would write a separate audit query in PQ.
create a new query
dim table first, fact table second, join type “all items on right, not in left”. Maybe called a left anti join - cant remember.
load to a table in Excel

alternatively, put both key columns in a pivot, plus count rows of the fact key in values, and filter on dim key = blank.

 

[Krash7000]

Thanks! I tested and both versions worked great (Although I believe it was right anti join - Just for anyone else that tries it)

I will go with the pivot version as it works nice and quickly, but good to know both ways. Thanks again.