lookup from another sheet with duplicates

[Anonymous]

Hi, i've got a bit stuck  in Power BI. I'm trying to use the lookup function. Upon trying i got the "a table of multiple values was supplied where a single value was expected". I guess that this is due to there beeing duplicate entries in my data (public records). anybody got a suggestion for a solution? 

So, what i'm trying to do is make Power bi look up a corresponding value to "lokalitetsnavn" in "column 16" in the second sheet, and then provide the matching information in "column 3" to the first sheet. This will tell me which farming company owns the specific license. 

The column lokalitetsnummer matches with "column 16" in the bottom sheet. However, not all numbers in column 16 is present in "lokalitetsnavn"

[Ashish_Mathur]

Hi,

There are duplicate occurencues in column3.  There is no mistake in your formula.  Please show the exact result that you are expecting epecially in cases where there are duplicate occurences.

[Anonymous]

Hi Ashish, 

I tried the formula again today and got the following error.

Which, I guess i relatively self explaining. The data in "column 3" is and needs to be text. Is there a workaround this? 

Both the corresponding values in "Column 16 and "Lokalitetsnummer" is numbers. Can transforming these values (which are uniqe id's identifying a particular site in both sheets) into text be a solutions? If so, how to do it?

Thank you, 

[v-piga-msft]

Hi @Anonymous,

Have you solved your problem after changing the type of Column 16 and "Lokalitetsnummer" to be text in Query Editor?

If you have solved your problem, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share some data sample which could reproduce your scenario and your desired output so that we could help further on it.

Best  Regards,

Cherry

[Ashish_Mathur · Accepted solution]

Hi,

In the Query Editor, you can change the data type to text.

[affan]

Hi @Anonymous

This seems that you are trying to use LOOKUPVALUE function in measure. Instead you should insert a calculated column and then use it in your report.

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

Regards,

Affan