LOOKUPVALUE with multiple results

[Sabsy]

Hi all,

I am new to Power BI so appologies if this is a silly question but I've been stuck on finding a solution and could really use some guidance. 

I have two tables that do not contain a unique key, One table contains value Customer Plan (new) and I would like to use it to look up against Customer Plan (old) in a different table to return a list of contracts. 

For example,

Table 1:

Customer_Plan (old)            Contract ID

123_A                                     A1

123_B                                     A2

123_B                                     A3

Table 2:

Customer Plan (new):

123_B

The idea is that I would like to generate an output that tells me that customer plan (new) "123_B" in table 2 has two contract IDs "A2" and "A3" when referencing customer plan (old) in table 1. The problem when I try and use function LOOKUPVALUE it returns multiple results and cannot display this in one column. My question is, how can I get this to either generate multiple columns (depending on the total number of results) or generate a new table of results so that I can get the output I am looking for. 

[Sabsy]

Thank you @PattemManohar for your suggestion, I have tried this and it has worked and provided all results into one cell.

Thank you @Ashish_Mathur for your suggestion to merge the queries. As I am quite new to Power BI, I wasnt familiar with how this worked but have managed to also replicate a solution using this function. My question now is that now I have structred the data in a way where i have a table of results within Table 1. Is there a way to print the contract ID list of results in table 1 with each contract ID having its own column. The customer/plan table may have a varied different number of results (max would be 10).

Table 1

Customer/Plan (As-Is) table (row 1) within Table 1

Example output...

Customer/Plan (As-Is)          Contract ID1       Contract ID2       Contract ID3     Contract ID4

8300002592_AF                   T-009141            T-001871             T-0018172      T-18191

Thanks.     

[Ashish_Mathur]

Hi,

Yes, that should be possible.  Paste the base data here so that i can copy that in Excel.

[Sabsy]

Hi,

Example data is below, you will need to merge tables 1 and 2 first.

Table 1
Customer/Plan (To-Be)
8300002592_AF
8300002602_AF
8300002222_AF

Table 2
Customer/Plan (As-Is)    Contract ID
8300002592_AF            T-0019141
8300002592_AF            T-0018171
8300002592_AF            T-0018172
8300002592_AF            T-0018122
8300002602_AF            T-0019149
8300002602_AF            T-0018177
8300002602_AF            T-0018194

Thanks,

David.

[Ashish_Mathur · Accepted solution]

Hi,

Hope this helps.  You may download my PBI file from here.

[MatthiasBI]

Hello Ashish,

read your post and was trying to implement as described but I received some error messages

I want to use your code to expand a table 1 of various columns (picture below shows only two columns for simplicity) with another table 2 through the common column "sector" . From table 2, I want to carry 3 columns (application, Units, Price) into the new table "desired result". I tried the following code in a new table, but was not succesfull:

Source = Table.NestedJoin(#"table1", {"Sector"}, #"table2",{"Sector"},#"result",JoinKind.LeftOuter)

Hope you can help. Thanks in advance!

[Ashish_Mathur]

Share some data to work with and show the expected result clearly.

[MatthiasBI]

thanks Ashish for getting back to me so fast, please see in  link below an xls sheet with table 1, table 2 and the desired result table.

What I want to accomplish is to get power bi to create a new table using table 1 and table 2 with the common column "sector", so basically expand table 1 with table 2.

I think that's exactly what your code is supposed to do but I think I'm doing something wrong - unfortunately I'm new to BI and still need to learn 😉

Thank you so much.

link to file 

[Ashish_Mathur]

Hi,

You may download my solution file from here.

Hope this helps.

[MatthiasBI]

Ashish, you literally saved my life!! Thank you so much , works like a charm!

[Ashish_Mathur]

You are welcome.  If my previous reply helped, please mark that reply as Answer.

[MatthiasBI]

Ashish, I tried, it only gives me the option "mark as new" - do you know where I find the link "mark as answer" and I will gladly do it...sorry , still new here 😉

[Sabsy]

Thank you, that was exactly what I was looking for! 🙂

[Ashish_Mathur]

You are welcome.

[blanca12]

if i can write a measure to get all reults for a particular ID in one cell (seperated by commas)?

______________________________________________________________

https://plex.software/

https://luckypatcher.pro/

https://kodi.software/

[blanca12]

if i can write a measure to get all reults for a particular ID in one cell (seperated by commas)?

______________________________________________________________

https://plex.software/

https://luckypatcher.pro/

https://kodi.software/

[Ashish_Mathur]

Hi,

Will Table2 have unique ID's?

[Sabsy]

It doesn’t at the moment but I could create a unique ID.

[Ashish_Mathur]

Hi,

Will it be OK, if i can write a measure to get all reults for a particular ID in one cell (seperated by commas)?

[Sabsy]

Yes, that can work as I can split them out manually after.

[Ashish_Mathur]

Hi,

The simple solution is to merge both Tables using the Query Editor.