Hello everyone,
I have two tables that have a relationship, I want to get a value from one of the tables ( say Table 1 for argument sake… ) and populate its contents on Table 2 in a new column:
I have a uploaded a sample data in excel
Adding A column.xlsx (10.6 KB)
any help would be much appreciated.
Hello @Geffmburu,
Thank You for posting your query onto the Forum.
Well we can definitely do this in Power Query by following the steps as suggested below alongwith the screenshots provided for the reference -
Step 1: Under the “Home” tab. Go to the “Merge Queries” option.
Step 2: Match your columns from Table 2 with the Table 1 using Order No.1 since it’s common between these 2 tables.
Step 3: Click on the “Expand” button
Step 4: Uncheck remaining options and select only “Finished Quantity” option.
Step 5: Result
I’m also attaching the Excel as well as PBIX file of the working for the reference.
Hoping you find this useful and meets your requirements that you’re looking for. 
Thanks and Warm Regards,
Harsh
Adding A column.xlsx (13.3 KB)
Power Query.pbix (33.5 KB)
Hello @Geffmburu,
If you don’t want to add 2 worksheets into the Excel files and want to create 2 tables directly into the Power Query. You can also achieve the same results by performing the steps as provided below alongwith the screenshots provided for the reference -
Step 1: Load the Excel file into the Query Editor
Step 2: Create a duplicate table of the same table loaded from the Excel file.
Step 3: Click on the drop-down button of the “Column1” and un-check the options as shown into the screenshot.
Step 4: We’ll have filtered rows as shown below.
Step 5: Under the “Home” tab. Go to the “Keep Rows” section. Select the “Keep Top Rows” option.
Step 6: Filtered Rows
Step 7: Under the “Transform” tab. Select the option “Use First Row as Headers”. Final result of Table A
For Table B, follow almost the similar steps. Here you’ll just be required to “Remove the Top 2 Rows” since it contains the Table 1 information.
Below is the screenshot provided of the end result for the Table B.
And then apply the same steps as mentioned in the previous post to add one more column of the “Finished Quantity”
I’m also attaching the Excel as well as PBIX of the working for the reference.
Thanks and Warm Regards,
Harsh
Adding A column - Harsh v2.xlsx (13.2 KB)
Power Query - Harsh v2.pbix (54.2 KB)
Hi @Geffmburu, did the response provided by @Harsh help you solve your query? If not, how far did you get, and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!
Hello @Harsh,
Thank you very much for the information, it has really resolved my problem.
I was trying to solve the problem through Dax and I was not able… Thank for this solution,
Just wondering…is the same solution possible through DAX? just for my learning
Sure the solution @Harsh gave solved my query…I’m grateful this forum is truly a worthy resource.
Thank Team !!!
Hello @Geffmburu,
Surely, this is doable via DAX. You can use a “Calculated Column” option or can write as a “Measure” as well. Firstly, I’ll be providing a formulas for both the options and then attaching the screenshot of the final result -
Option 1: Calculated Column
So in Table B, we already have a “Finished Quantity” column which we retrieved yesterday using Power Query. Now, in the same table I’ll be adding a column using “Calculated Column” option. Below is the formula provided for the reference -
Finished Quantity 2 =
LOOKUPVALUE( 'Table 1'[Finished Quantity] ,
'Table 1'[Order No.] , 'Table B'[Order.No.] )
Option 2: Rather than adding a “Calculated Column” into the data model, we can do this via “Measure” as well. Below is the formula provided for the reference -
Finished Quantity =
LOOKUPVALUE( 'Table 1'[Finished Quantity] ,
'Table 1'[Order No.] , SELECTEDVALUE( 'Table 2'[Order.No.] ) )
Now, attaching the screenshot of the final result wherein you’ll observe that both the results are identical and there’s absolutely no difference at all in the final results. But then you may witness “Grand Totals” are different using “Calculated Columns” and “Measures”.
So if you don’t want to “Summarize” the calculated column then just change the option as shown below into the screenshot -
And the “Total” will disappear but the same option is not available for “Measures”. Below is the screenshot provided of the result for the reference -
I’m also attaching the working of the PBIX file for the reference. So I guess we’ve explored all the options. i.e. using Power Query, Calculated Column and Measures.
Hoping you find this useful.
Thanks and Warm Regards,
Harsh
Power Query.pbix (57.2 KB)
@Harsh, This is very helpful information for my learning, I do appreciate for the help
Kind Regards
Geff Mburu
Hello @Geffmburu,
You’re Welcome.
I’m glad that I was able to help you.
Thanks and Warm Regards,
Harsh
