Comparing 2 datasets for "missing" data

I am working with a series of large datasets and converting them to using table relationships rather than vlookup within the rows of data. My reference table is the "master" and needs to contain all...

BI & Data Analysis

excel

Formulas and Functions

office 365

Wyn Hopkins
Mar 19, 2020
Hi Jo1963

Normally you pull in your Row and Column fields from the Lookup Table.

In this case the missing "N/A#" equivalents will show up as blank like this

So you could set up a Pivot Table with a Filter on for Product Name = Blank and also showing Product Code from the Fact / Transaction table. Then if anything is missing it will display.

You could also add a calculated column to your Fact table =ISBLANK( RELATED( ProductTable[Product Name] ) ) and it will show TRUEs wherever it's missing.

Let me know if that does / doesn't make sense

Cheers

Hope that helps

Wyn

MVP

Australia UTC+ 8

If this answer was the best response please click the button

I also happily accept likes

Wyn Hopkins

MVP

Mar 19, 2020

Hi Jo1963

Normally you pull in your Row and Column fields from the Lookup Table.

In this case the missing "N/A#" equivalents will show up as blank like this

So you could set up a Pivot Table with a Filter on for Product Name = Blank and also showing Product Code from the Fact / Transaction table. Then if anything is missing it will display.

You could also add a calculated column to your Fact table =ISBLANK( RELATED( ProductTable[Product Name] ) ) and it will show TRUEs wherever it's missing.

Let me know if that does / doesn't make sense

Cheers

Hope that helps

Wyn

MVP

Australia UTC+ 8

If this answer was the best response please click the button

I also happily accept likes