SOLVED

Comparing 2 datasets for "missing" data

%3CLINGO-SUB%20id%3D%22lingo-sub-1236461%22%20slang%3D%22en-US%22%3EComparing%202%20datasets%20for%20%22missing%22%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1236461%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20working%20with%20a%20series%20of%20large%20datasets%20and%20converting%20them%20to%20using%20table%20relationships%20rather%20than%20vlookup%20within%20the%20rows%20of%20data.%26nbsp%3B%20My%20reference%20table%20is%20the%20%22master%22%20and%20needs%20to%20contain%20all%20the%20combinations%20to%20produce%20the%20correct%20result(s)%20in%20the%20pivot%20table.%26nbsp%3B%20My%20data%20set%20currently%20has%20cells%20in%20each%20row%20that%20do%20a%20lookup%20%26amp%3B%20return%20either%20a%20matching%20confirmation%20or%20the%20infamous%20%23N%2FA%20%3A).%26nbsp%3B%20If%20an%20%23N%2FA%20is%20returned%20its%20tells%20me%20I%20need%20to%20set%20up%20a%20new%20combination%20in%20the%20reference%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20if%20I%20move%20away%20from%20using%20the%20vlookup%20in%20each%20row%20of%20data%2C%20how%20will%20I%20know%20(in%20the%20resulting%20pivot%20table)%20that%20I'm%20excluding%20data%20because%20there%20is%20no%20match%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1236461%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1237314%22%20slang%3D%22en-US%22%3ERe%3A%20Comparing%202%20datasets%20for%20%22missing%22%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1237314%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F586179%22%20target%3D%22_blank%22%3E%40Jo1963%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENormally%20you%20pull%20in%20your%20Row%20and%20Column%20fields%20from%20the%20Lookup%20Table.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20this%20case%20the%20missing%20%22N%2FA%23%22%20equivalents%20will%20show%20up%20as%20blank%20like%20this%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%20id%3D%22tinyMceEditorWyn%20Hopkins_0%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F178046iF8CF521496A106E8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%20you%20could%20set%20up%20a%20Pivot%20Table%20with%20a%20Filter%20on%20for%20Product%20Name%20%3D%20Blank%20and%20also%20showing%20Product%20Code%20from%20the%20Fact%20%2F%20Transaction%20table.%26nbsp%3B%20Then%20if%20anything%20is%20missing%20it%20will%20display.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20could%20also%20add%20a%20calculated%20column%20to%20your%20Fact%20table%26nbsp%3B%20%3DISBLANK(%20RELATED(%20ProductTable%5BProduct%20Name%5D%20)%20)%26nbsp%3B%20and%20it%20will%20show%20TRUEs%20wherever%20it's%20missing.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet%20me%20know%20if%20that%20does%20%2F%20doesn't%20make%20sense%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECheers%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3EHope%20that%20helps%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3EWyn%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3EMVP%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3EAustralia%20UTC%2B%208%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%3CSPAN%20style%3D%22font-style%3A%20italic%3B%22%3EIf%20this%20answer%20was%20the%20best%20response%20please%20click%20the%20button%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%3CSPAN%20style%3D%22font-style%3A%20italic%3B%22%3EI%20also%20happily%20accept%20likes%20%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1246573%22%20slang%3D%22en-US%22%3ERe%3A%20Comparing%202%20datasets%20for%20%22missing%22%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1246573%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E-%20thank%20you%20!%26nbsp%3B%20I'm%20using%20the%20ISBLANK%20formula%20%26amp%3B%20it%20works%20like%20a%20charm%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1246947%22%20slang%3D%22en-US%22%3ERe%3A%20Comparing%202%20datasets%20for%20%22missing%22%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1246947%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E-%20so%20I%20must%20be%20doing%20something%20wrong%20with%20the%20isblank%20formula.%26nbsp%3B%20All%20I%20am%20getting%20is%20FALSE%20on%20my%20fact%20table%20%26amp%3B%20I%20know%20this%20is%20not%20correct.%20I%20have%20attached%20a%20simplified%20version%20(and%20put%20the%20reference%20and%20fact%20tables%20on%20one%20sheet%20-%20can%20you%20advise%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1247089%22%20slang%3D%22en-US%22%3ERe%3A%20Comparing%202%20datasets%20for%20%22missing%22%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1247089%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F586179%22%20target%3D%22_blank%22%3E%40Jo1963%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECalculated%20column%20is%20to%20be%20added%20in%20data%20model%2C%20not%20in%20source%20table%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1247474%22%20slang%3D%22en-US%22%3ERe%3A%20Comparing%202%20datasets%20for%20%22missing%22%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1247474%22%20slang%3D%22en-US%22%3E%3CP%3EOh%20of%20course%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%20-%20thank%20you%20for%20that%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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 ?

5 Replies
Highlighted
Best Response confirmed by Jo1963 (Occasional Contributor)
Solution

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

 

 

image.png

 

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

 

Highlighted

@Wyn Hopkins- thank you !  I'm using the ISBLANK formula & it works like a charm  

Highlighted

@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

Highlighted

@Jo1963 

Calculated column is to be added in data model, not in source table

Highlighted

Oh of course @Sergei Baklan - thank you for that