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

21 REPLIES 21
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.

Thank you dear for this useful demo , I have very big data, I tried it but when I implemint it the custom column take long time and not responed at all , do you think this method not useful for big number of rows? are there another solution other than dax? 

Did you ever find a solution to this being so slow?  I also have big data and whether I do a merge as new or use the suggested solution, it takes forever....

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!

arnabmit
Frequent Visitor

I am too getting same error. Any solution?

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

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]

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors