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

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!!

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!

Great post.

(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

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.

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?

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?

Thanks for post, it helped a lot. 

thank you so much, you saved my life!

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?