topic Re: LOOKUPVALUE And Return Multiple Values Horizontally in Power Query

LOOKUPVALUE And Return Multiple Values Horizontally

bishnu — Sat, 29 Feb 2020 16:49:01 GMT

Hello

Is it possible to stack mutiple values horizontally using LOOKUPVALUE in powerbi. i saw its possible in excel using INDEX, so i tried with SUBSTITUTEWITHINDEX, with no luck

following is the link for excel solution

https://www.extendoffice.com/documents/excel/2623-excel-vlookup-return-multiple-values-horizontally.html

Thanks in advance

Re: LOOKUPVALUE And Return Multiple Values Horizontally

Greg_Deckler — Sat, 29 Feb 2020 17:39:04 GMT

Are you trying to do this in a table, a visual or ? Can you provide more information? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Re: LOOKUPVALUE And Return Multiple Values Horizontally

bishnu — Sat, 29 Feb 2020 21:15:38 GMT

Hello Greg

Sorry that i structured it badly. I will try to explain in a better way

Below is the table A

So table A should check for Criteria in Table B (below)

If the criteria matches and the Status is "Verified", it shoud give me data correspoding to marching criteria horizontally (As highlighted in Table A) if status is "not verified" 0 or NA

My idea was to use use LOOKUPVALE, but i dont know how to use in combination (i am a newbee with powerbi)

Thanks in advance

Re: LOOKUPVALUE And Return Multiple Values Horizontally

Greg_Deckler — Sun, 01 Mar 2020 01:06:55 GMT

OK, bishnu I'm trying to stick with you here. Can you provide me your expected results from the example data? From what I understand, you for each Criteria in Table A, you want to make sure that the values that appear in Doc1, Doc2, and Doc3 all appear in Table B with a Status of verified, is that close?

Re: LOOKUPVALUE And Return Multiple Values Horizontally

bishnu — Sun, 01 Mar 2020 19:58:30 GMT

Hello greg

Thank you

The expected results are highlighted in green for Doc1, Doc2, and Doc3 in table A. I didnt know how to do it, so i entered it manually so that i what i wants as my result.

So basically the table B status will change, say like "N\A, Not verified, or Verified" but obviously final result will be "Verified".

I am trying to create this table A in powerbi so that i can keep a track of "N\A, Not verified, or Verified" doc's(documents) corresponding to each criteria.

i didnt understand what you meant by close!

Regards

Re: LOOKUPVALUE And Return Multiple Values Horizontally

Greg_Deckler — Sun, 01 Mar 2020 20:18:05 GMT

OK, do you have an Index column for your source data table or can you add one?

Re: LOOKUPVALUE And Return Multiple Values Horizontally

bishnu — Sun, 01 Mar 2020 20:24:30 GMT

Hello Greg

As if now i didnt add index column for both Table A or source data Table B. But i can if its needed

Re: LOOKUPVALUE And Return Multiple Values Horizontally

Greg_Deckler — Sun, 01 Mar 2020 21:36:44 GMT

OK, if you do not need them to be in individual columns, you can do this:

Measure = CONCATENATEX(DISTINCT(FILTER('Table',[Status]="verified")),[Code],",")

See attached PBIX file.