How can I create custom column to bring in a value (lookup) from another table in Power Query Editor

[UK06B1]

Hello All,

I am having issues with the following. I have 2 tables in Power BI (I have simplified for the sake of the example). 

Table 1

Main activity Type	Prioritisation
A1	P1
A2	P2
A3	P1

Table 2 

Main activity Type	Cost	Prioritisation
A1	22
A2	32
A2	21

Is it actually possible in the Power Query editor in PowerBI to use a Dax/M formula to bring the Prioritisation into table 2 ? in the example above the resuting table would be 

Table 2

Main activity Type	Cost	Prioritisation
A1	22	P1
A2	32	P2
A2	21	P2

Its worth noting that i have managed to do this in the in the add custom column in the table in Power BI (see screen shot of the live formula i used) but i would have preferred to have it in the query as i need to do a few other things after the column is created - with dates etc. 

Is this possible? any help would be greatly appreciated. 

[BA_Pete · Accepted solution]

Hi @UK06B1 ,

Is there a specific reason why you are avoiding using a left outer merge on

Table2[MainActivityType] = Table1[MainActivityType] ?

This is simple (can be done completely from the GUI) and can give a significant performance enhancement (will fold back to SQL server if both merged tables fully fold) over a coded approach.

Pete

[v-angzheng-msft · Accepted solution]

Hi, @UK06B1 

This feature Merge will help you.

Follow the steps below:

Result:

If there are values here that fail to match successfully, then the cells have different data, please remove the extra invisible characters, such as spaces, to ensure that the data is the same, and then this will work for you.

If you still don't get the results you want, could you please consider attaching your data by using the copy table in PowerQuery? It makes it easier to give you a solution.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[UK06B1]

hi thanks for your help and ahve accepted soltuion. thanks for your help. 

[UK06B1]

Hi all apologies for the delay in replying - help really appreciated and have managed now to sort with your support and help.  

Thanks again .

Neil

[BA_Pete · Accepted solution]

Hi @UK06B1 ,

Is there a specific reason why you are avoiding using a left outer merge on

Table2[MainActivityType] = Table1[MainActivityType] ?

This is simple (can be done completely from the GUI) and can give a significant performance enhancement (will fold back to SQL server if both merged tables fully fold) over a coded approach.

Pete

[UK06B1]

Hi Pete 

I just played around with this and i think i might have cracked it using what you sugested. I chose the 2 tables and selected the merge option - then chose the 2 columns in the 2 tables - it then created a new table where i think its added the 3 columns from the mapping table to the original data . See Below  - is this the solution you meant ? 

I need to do some data checking but if you could confirm i have done what you were suggesting  - just want to make sure i have done it the most efficient way for performance of the model - as its quiet a hefty model. 

Cheers 

Neil 

[BA_Pete]

Hi @UK06B1 ,

Yes, that looks like the method I was suggesting.

A couple of things:

1) You don't necessarily need to create a new merged table. You can choose to just merge the columns from table2 onto the original table1. Either way, you can now reduce the work done during refresh and the size of your PBIX file by right-clicking on your source table(s) in Power Query and unchecking 'Enable Load'. This will still allow the source query(ies) to refresh to provide new data for the merge, but will prevent the source table(s) from being loaded into your model.

2) Regarding performance, impossible for me to advise on this as there's so many specific variables that go into deciding whether it's the best way for you and your model. However, I would say: don't disregard the very short amount of time it's taken you to implement; the simplicity of the process itself and; the ease with which it can be maintained in future by you or someone else, when assessing the overall 'efficiency' of this method over others.

Pete

[UK06B1]

Cheers Pete, understand about performance . 

I just checked the Data and it appears that some of the rows dont have the value from the mapping table. See screen shot  - In th merged data i just filtered by Null in the Main.ActivityType.Prioritisation - and for these rows the activity type is WM in the data table but also it exisits in the mapping table. Is there anything obvious i might have missed.

Note, I deselcted the fuzzy merge option and also once the table had been created the selections i chose are in the 2nd screen shot - i just licked ok to expand the data. I do not want the data to be aggregated as i need all the individual line items . I also checked if there were any spaces after the Act Type in the mapping table. And they are exact in both tables. 

Is there anything obvious i could be missing? 

Cheers 

Neil 

[BA_Pete]

Hi @UK06B1 ,

Difficult to say definitively without seeing the PBIX, but my first shot would be the space in "WM ".

You could try trimming (select column Transform tab > Format > Trim) both of your merge columns prior to the merge step to see if that cleans up the matches between them.

Pete

[BA_Pete]

Hi @UK06B1 ,

Just re-reading this thread, and I noticed that you said you "chose the 2 columns in the 2 tables" to do the merge.

Based on your example data, you should only need to choose one column from each table to merge on.

Have I misinterpreted your statement? If not, then do the merge again choosing only Table2[Main Activity Type] and Table1[Main Activity Type] to merge on.

Pete

[UK06B1]

Hi Pete , 

Thanks for you prompt response. The 2 tables are completely different tables and about 20 or so colums in each table. Would the merge function still work ? is this the function i would use to do what you suggest ? 

If you could show me steps what i need to do i will give it a try. 

cheers

Neil