Lookupvalue Returning an Error

[Cellblime]

I'm new to Power BI, but I believe I can describe this issue accurately.  I have a queried table, Letters, with a column, Letter Code.  I also have an imported Excel table, Letter Descriptions, with two columns, Description and Letter Code.  I'm attempting to add a column to the Letters table that will use LookUpValue to pull the Description column values from the Letter Descriptions table over by matching the Letter Code columns.

Letter Description = (LOOKUPVALUE('Letter Descriptions'[Description],'Letter Descriptions'[Letter Code], Letters[Letter Code]))

I receive an error, "A table of multiple values was supplied where a single value was expected.".  My understanding is that this means there are duplicate values on the Letter Descriptions table, however there are no duplicates/blanks.  The data type on both tables is Text.  The Letter Code column on the Letters table WILL have duplicates because there are multiple letters sent each day of the same letter code, however, this seems like it should work like vlookup on Excel and duplicates on the Letters table shouldn't cause an issue with applying the same description to the same letter codes.

Can you explain where I went wrong, or why this, seemingly easy lookup, is causing a problem?

[Bibiano_Geraldo · Accepted solution]

Hi @Cellblime ,
It sounds like you’re on the right track with using LOOKUPVALUE, but the error you’re encountering typically means that there are multiple matching values in the Letter Descriptions table for a given Letter Code. Double-check that the Letter Code column in the Letter Descriptions table truly has unique values.

Use FIRSTNONBLANK BUT NOTE: This will only return the first non blank value found, ignoring all other's.

Letter Description = 
CALCULATE(
    FIRSTNONBLANK('Letter Descriptions'[Description], 1),
    FILTER('Letter Descriptions', 'Letter Descriptions'[Letter Code] = Letters[Letter Code])
)

 
I hope this help you, if so, please accept as solution and give a Kudo.

Thank you.

[Cellblime]

That worked, thanks!  I had tried something similar that ChatGPT provided (see below), but I was getting an error.  Your help is much appreciated!

Letter Description =
CALCULATE(
FIRSTNONBLANK(LetterDescriptions[Letter Description], 1),
LetterDescriptions[Letter Code] = Letters[Letter Code]
)

[Ashish_Mathur]

Hi,

Ensure that your formula is a calculated column formula (not a measure).  If this does not help, then share some data to work with. Share data in a format that can be pasted in an MS Excel file.

[PhilipTreacy]

@Cellblime 

Please supply some sample data. 

You describe the Letters table as only having 1 column?  But is that really the case?  What is the point of that table?

In this case what is a Letter?  Something like A, B etc or a written correspondence?

From what you are trying to do it sounds like you will end up with the same table in both cases?  Both containing Letter Description and Letter Code?

Regards

Phil

[Cellblime]

The Letters table has a few additional fields, I was just trying to avoid complicating the question. The Letters table data is queried and there is no description of the letter code on the database so I created a separate Excel sheet with the description and corresponding letter code, and imported it so I could add the missing description to the Letters table. The Letters table has the following fields, Letter Date, Letter Code, Delivery Method and Customer Number. The Letter Code is a 4 character value with one letter and three numbers, like A123.

[ryan_mayu]

It's because one code may has differetn description. You need to double check your data to see if there is any space that caused this issue. 

If the issue can't be fixed, pls provide some sample data and expected ouptut, or provide your pbix file