Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sabsy
Frequent Visitor

LOOKUPVALUE with multiple results

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. 

 

1 ACCEPTED SOLUTION

Hi,

 

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

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

24 REPLIES 24
Sabsy
Frequent Visitor

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

2018-10-18 08_50_29-Jigsaw PBI New Model - Power Query Editor.png

 

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

2018-10-18 08_56_01-Jigsaw PBI New Model - Power Query Editor.png

 

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.     

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

Hi,

 

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

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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!

 

MatthiasBI_0-1666814121763.png

 

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 

Hi,

You may download my solution file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 😉

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

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
blanca12
New Member

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
New Member

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
Super User
Super User

Hi,

 

Will Table2 have unique ID's?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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)?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.