Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 where a single value was expected." I cant understand why this happens. I've tried to change the [Name] column to other columns in that table and some are working and some are getting the error. What am I missing here?
Table called DataCheckNav_vs_SF
My first column is a summarize of our Customer Data in NAV;
Solved! Go to Solution.
@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 @Gugge,
Can you please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hi,
I recreated a excel file that gives me the same error, I'm unsure on how to upload a file here so i uploaded it to a file sharing site. https://ufile.io/hnbqmwg0
All the data is fake, but it gives me the same error when trying to create the table with the below formulas, the first three works but the last one gives the error "A table of multiple values was supplied where a single value was expected."
DataCheckNav_vs_SF =
SUMMARIZE
(NAV_CustomerData,
NAV_CustomerData[NAV_FAKEno])
NAV_Name2 = LOOKUPVALUE(
NAV_CustomerData[NAV_Fakename2],
NAV_CustomerData[NAV_FAKEno],
DataCheckNav_vs_SF[NAV_FAKEno]
)
SF_ERP no =
LOOKUPVALUE(
SF_Account[SFFAKE_ERPID],
SF_Account[SFFAKE_ERPID],
DataCheckNav_vs_SF[NAV_FAKEno]
)
SF_name =
LOOKUPVALUE(
SF_Account[SFFAKE_name],
SF_Account[SFFAKE_ERPID],
DataCheckNav_vs_SF[NAV_FAKEno]
)
Hi @Gugge
Would this not be eaiser in the query editor, where you can merge and create tables with the required columns a bit eaiser.
Hi, thank you for the answer. It might be easier, but I do not have the knowledge on how to solve that with a query.
Morning @Gugge
It is very easy to do this in the Query Editor, Merging tables and choosing your columns, if you do not have a unique ID for these, you can also create one.
LOOKUPVALUE often gives that message when it scans the table and it is not clear which result it can find as there are several options.
If you want to find more about this, try these videos.
SQLBI Understanding lookupvalue
If you want some help with the Query way, I would be happy to guide you if you provide some dummy data.
@Gugge SUMMARIZE returns a table. Are you trying to use this to generate a Calculated Column? It is probably giving you the error.
Thanks you for your answer.
This is how it looks. I've created a table by using SUMMARIZE, then I want to expand the table to be able to cross check information. The first three LOOKUPVALUE columns work, I added in one just to show what happens. As seen in the picture, when there is no result it returns a blank cell and when it is a result it returns the data. But for the Name column, all I'm getting is #ERROR and the message "A table of multiple values was supplied where a single value was expected."
@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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |