Forum Discussion
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 the combinations to produce the correct result(s) in the pivot table. My data set currently has cells in each row that do a lookup & return either a matching confirmation or the infamous #N/A :). If an #N/A is returned its tells me I need to set up a new combination in the reference table.
So if I move away from using the vlookup in each row of data, how will I know (in the resulting pivot table) that I'm excluding data because there is no match ?
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
5 Replies
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- so I must be doing something wrong with the isblank formula. All I am getting is FALSE on my fact table & I know this is not correct. I have attached a simplified version (and put the reference and fact tables on one sheet - can you advise
Calculated column is to be added in data model, not in source table
Wyn Hopkins- thank you ! I'm using the ISBLANK formula & it works like a charm 🙂
