cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Shelley
Continued Contributor
Continued Contributor

How to lookup values in another table in the Query Editor?

Does anyone know if/how this can be done?

 

I have a table in the Query Editor that has a number of fields, one of which is the Customer #. I want to make a new column in this table that contains Region, by looking up the Customer # in another table that contains Customer Master data. Is there an easy way to do this? I was thinking if I could do this, it would perform faster than if I used DAX after the tables were brought into the file. Here's an example of the query for the orders file for which I'd like to use the customer # to lookup the Region in that other table.

 

let
Source = Sql.Databases("csmdataservice.cloudapp.net"),
CSM_Master = Source{[Name="CSM_Master"]}[Data],
dbo_OrdersView = CSM_Master{[Schema="dbo",Item="OrdersView"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(dbo_OrdersView,{{"Line_Creation_Date", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Line_Creation_Date] >= #date(2016, 10, 1)),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each [Sold_To_APR] & "|" & [Sold_To_Party_Description]),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "%APRBPIDKey"}})
in
#"Renamed Columns"

 

Any help is appreciated. Seems @ImkeF is really good with M.  : )

1 ACCEPTED SOLUTION
v-jiascu-msft
Microsoft
Microsoft

Hi @Shelley,

 

Please check out the demo in the attachment. It's a solution in Power Query (the Custom column).

(let currentCustomer = [CustomerName] in Table.SelectRows(Table1, each [CustomerName] = currentCustomer)){0}[CustomerID]

how_to_lookup_values_in_another_table_in_the_query_editor

Best Regards,
Dale

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

View solution in original post

18 REPLIES 18
v-jiascu-msft
Microsoft
Microsoft

Hi @Shelley,

 

Please check out the demo in the attachment. It's a solution in Power Query (the Custom column).

(let currentCustomer = [CustomerName] in Table.SelectRows(Table1, each [CustomerName] = currentCustomer)){0}[CustomerID]

how_to_lookup_values_in_another_table_in_the_query_editor

Best Regards,
Dale

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

View solution in original post

thank you so much, you saved my life!

Thanks for post, it helped a lot. 

Thank you so much! This has helped me greatly. Can you please explain the logic in this power query custom column ( such as what does the "{0}" do in the query?). Also, how does this compare to using Power Query Merge?

I am wondering the same thing.

 

Specifically, since we can either use the custom lookup or the merge to achieve the desired result...would like to know which one is better in terms of efficiency and performance.

 

@MFelix  @v-jiascu-msft  @Chihiro 

 

Can anyone provide some insight? Thank you for your help!

Anonymous
Not applicable

Hi, I tried this but it made my already big table (1giga) became >1 tera. And paradoxally i did the step to lookup a column in another table to make the initial 1giga table smaller by filtering on the custom column.

Is there way to reduce the table as I want before closing the query but not making the upload of the query so slow?

@v-jiascu-msft 

 

I tried to apply the formula to create a custom "lookup" column but it fails.

 

(let QId = [Q-C] in Table.SelectRows(Metadata, each [Q-C] = QId)){0}[Question]

Error: Invalid identifier and red line appear below "in" in above formula.

 

I have "Metadata" table that contains columns Q-C, Questions.

In another table "QwC" I have column Q-C and where ever value of "QwC-QC" matches "Metadata-Q-C", populate the custom column "Question" in "QwC" with value of "Metadata-Question"

 

Thank you in advance for any help in this regard.

Shelley
Continued Contributor
Continued Contributor

@v-jiascu-msft Thanks for your help in the past. I am now trying to lookup a profit center description with a profit center code and am getting an error.

 

Here's the M:

let
    Source = Sql.Databases("csmdataservice.cloudapp.net"),
    CSM_Master = Source{[Name="CSM_Master"]}[Data],
    dbo_ProfitCenterGrouping = CSM_Master{[Schema="dbo",Item="ProfitCenterGrouping"]}[Data],
    #"Added Custom" = Table.AddColumn(dbo_ProfitCenterGrouping, "%Section|ProductLineKey", each [Section] & "|" & [Product_Line_FIN]),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Core?", each if [Flag_Core] = true then "Core" else "NonCore"),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column",{"PC", "Product_Line_FIN", "Section", "%Section|ProductLineKey", "Core?"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns", {"Product_Line_FIN"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"Product_Line_FIN", "Product Line"}, {"Section", "Product Group"}}),
    #"Added Custom2" = Table.AddColumn(#"Renamed Columns", "Custom", each (let currentProfitCenter = [PC] in Table.SelectRows(OrdersView_Profit_Center_Description_Lookup, each [Profit_Center] = currentProfitCenter)){0}[Profit_Center_Description])
in
    #"Added Custom2"

 

I'm trying to look up the profit center code [PC] from this table into the Ordersview_Profit_Center_Description_Lookup reference table (field = Profit_Center) to get the Profit_Center_Description value, but am receiving this error:

Expression.Error: There weren't enough elements in the enumeration to complete the operation.
Details:
    Table

 

What the heck am I doing wrong? Thanks for your help!

(let currentCustomer = [CustomerName] in Table.SelectRows(Table1, each [CustomerName] = currentCustomer)){0}[CustomerID]

 

Intead of CustomerID if i want a static text say "Cust" to be entered in the custom column then what is the syntax to achieve this.

Also if the customer name present in table 2 is not present in table 1 I want to add another text say "NA".

 

For example if there is a customer G in table 2 and no entry for customer G in table 1 then I want to enter value as "NA" in custom column.

 

Thanks & Regards,

Sonam

Shelley
Continued Contributor
Continued Contributor

@v-jiascu-msftThanks for the help, Dale! I got it to work!

 

I was experiencing a couple errors due to bad data.

1. I discovered a data type mismatch - i.e. the data type on the distributor ID in one table was different than the other table - so I was receiving an error. I changed the data types to match and then this part worked.

2. Some of the distributor IDs were null or not in the master table and so some of the cells in the column again gave the same error. I didn't need these records, so I removed the errors.

 

I don't remember the exact wording of the error - something about enumerations. (I had this all typed out and then Microsoft took the site down for maintenance and everything I wrote was lost.)

 

THANKS AGAIN!!

Chihiro
Solution Sage
Solution Sage

You could  use standard Merge Query UI tool.

 

Or in "M" something like...

#"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"KeyColumnName"},LookupTableName,{"RelatedColumnName"},"NewColumnName",JoinKind.LeftOuter)

 

 Then expand Region.

MFelix
Super User III
Super User III

Hi @Shelley,

You ned to merge the two tables by Customer # and then expand only the Region column.

Check the link below

http://radacad.com/append-vs-merge-in-power-bi-and-power-query

However if you have a master data with customer information you just need to make the relationship between both tables on PBI and no need for dax or M to have it working. Creating the additional column on your table will add size to your model and repetead information assuming that the table where you want to add the region as more than 1 time the same customer.

Regards,
MFelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Shelley
Continued Contributor
Continued Contributor

The reason I want to do this is because I need to make a Key column that is Customer#|Region. I am trying to tie a whole bunch of fact tables together with a Link Table, using the key I create from customer# + region. If I don't create the key in the Query Editor, I was thinking the DAX will be slower.

Hi @Shelley,

Is the customer region ID column you want to create have the sam customer for more tha 1 region?

Not sure of the expected result and model you have but you could create an additional column with region only and make also a relation between this and the fact tables.

Can you give more insight on your data and expected result?

Don't advise on create additional columns of Customer/region ID since i will bring size and complexety to your model.
Regards
MFelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Shelley
Continued Contributor
Continued Contributor

Yes, the same customer# can be responsible for more than one region.  I also have a number of diverse fact tables. So I need the unique key to tie all the fact tables together correctly.

Hi @Shelley,

The using merge you can select what columns to show after merge and then you can delete the additionals after making the ID column.

However believe that the best setup is not.this.you should have a customer.table as you have and a region table thenake the.connection between tables.using.this.two.tables.

Can you share some.sample.data and expected result?

Regards.
MFelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Methods outlined in previous posts will handle it.

 

You just need to add another column/step to concatenate column values.

 

Using Column Merge tool, or adding custom column = [Column1] & [Column2]

Shelley
Continued Contributor
Continued Contributor

Chihiro, thanks for the input. Doesn't the Merge feature though make a big table with columns from both? In the end, I only want to add a key column. Would I merge them and then delete all the extra columns? I guess I've never really grasped Power BI's merge and append functions. They seem to work a little differently than I expect whenever I've tried to use them. 

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors