How Can I Lookup Data In A Table When There Is Not A Match for Every Row?

[whalensdad]

I have a table called raw data with a column that contains a document number and an original document type. I have another table that lists a subset of the document numbers and identifies what type of document it really is (Let’s call it “Corrected_Document_Type”). This second table does not contain a row for every document number. I want to add a calculated field to the raw data table that would show the corrected document type from the second table when there is a match between the document numbers and the original document type when there is no match. I am using PowerPivot for Excel 2010 with PowerPivot 10.50.1600.1

 

[Ozeroth]

Using your existing table setup, to add a calculated column to RawData you could:

1. Create a relationship from CorrectionTable[Doc Number] to RawData[Doc Number]. (This will work because RawData lists each document exactly once).
2. The DAX to lookup the Corrected Document Type (in a calculated column in RawData) would be
   = CALCULATE( VALUES( CorrectionTable[Corrected Document Type] ) )
   This will return BLANK if the document isn't in CorrectionTable. I haven't worried about handling multiple matches in the CorrectionTable since I assume that wouldn't happen.
3. I would confirm that this formula works, then wrap it in an IF function to use the corrected document type if it is not blank, otherwise use the original document type.

 

[whalensdad]

Works like a champ. THANKS!