Forum Discussion
Solved
Issue with LOOKUPVALUE giving error message
Hi, I'm trying to make a simple table with data from other tables with SUMMARIZE and LOOKUPVALUE. But when using that LOOKUPVALUE-formula, I'm getting the "A table of multiple values was supplied wh...
Guggemy best guess is SF_Account[Name] has multiple values against each unique SF_Account[ERP_ID__c].
Error Reproduced here
/* table name _left*/ let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}) in #"Changed Type"/* table name _right*/ let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWBsgwMwGwnINsIzHIGsoyVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ValA = _t, ValB = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ValA", type text}, {"ValB", Int64.Type}}) in #"Changed Type"Now, try this as calculated column in _left. You will see the issue.
Column = LOOKUPVALUE(_right[ValB],_right[ValA],_left[Column1])
Guggemy best guess is SF_Account[Name] has multiple values against each unique SF_Account[ERP_ID__c].
Error Reproduced here
/* table name _left*/
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}})
in
#"Changed Type"/* table name _right*/
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWBsgwMwGwnINsIzHIGsoyVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ValA = _t, ValB = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ValA", type text}, {"ValB", Int64.Type}})
in
#"Changed Type"
Now, try this as calculated column in _left. You will see the issue.
Column = LOOKUPVALUE(_right[ValB],_right[ValA],_left[Column1])
Hi,
Recreated this and you are correct. I have some duplicate values in my SF_Account column ERP_No. Removed them from my excel test I posted here and it works.
Still strange that some columns works and some doesn't, but now I know where to start when solving this. Thank you!