LOOKUPVALUE - "A table of multiple values was supplied where a single value was expected"

[X3svfdB1HOjKYc0]

I have a lookup that has multiple values and gives the standard error message. Instead of getting an error, is it possible to get a comma delimeted list of the multiple values in a single field?

Instead of a workaround, it would be really cool to have a function LOOKUPVALUES for this.

[v-stephen-msft]

Hi @X3svfdB1HOjKYc0 ,

Did you want to split columns in power query? You can split a column through different methods and get single value you want.

Or try to use List.Contains function in power query to match value.

Reference: Solved: HELP: Matching a value in one column to a value fr... - Microsoft Power BI Community

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vijay_A_Verma]

You can use CONACATENATEX combined with FILTER like below

=CONCATENATEX(FILTER(Table1,Table1[Name]=Table2[Name]),Table1[Item],", ")

Here, I am applying filter on Table1, Name column with Table2, Name field. Then I am concatenating resultant Table1, item column with comma separator.

[Bella42]

Awesomely brilliant. I always look for these clever one liners, there is so much unecessarily complicated DAX out there.

I was taught in the 80s that good code should be aiming for Haiku, not an epic poem 🙂 and it should always make you smile. This made me smile.

Thank you 🙏

[wdx223_Daniel]

you can try CONCATENATEX+CALCULATETABLE