Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
Hi,
Hope this helps. You may download my PBI file from here.
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.
Hi,
Yes, that should be possible. Paste the base data here so that i can copy that in Excel.
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.
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!
Share some data to work with and show the expected result clearly.
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.
Hi,
You may download my solution file from here.
Hope this helps.
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.
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.
if i can write a measure to get all reults for a particular ID in one cell (seperated by commas)?
______________________________________________________________
if i can write a measure to get all reults for a particular ID in one cell (seperated by commas)?
______________________________________________________________
Hi,
Will Table2 have unique ID's?
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)?
Hi,
The simple solution is to merge both Tables using the Query Editor.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |