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

[Shelley]

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.  : )

[v-jiascu-msft · Accepted solution]

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]

Best Regards,
Dale

[ThomasWeppler]

Great post.

[MAAbdullah_47]

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? 

[adriannabell]

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

[Anonymous]

thank you so much, you saved my life!

[Mani_0962]

Thanks for post, it helped a lot. 

[PowerBIUser9901]

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?

[Doodle]

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]

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?

[Anonymous]

@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]

@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]

I am too getting same error. Any solution?

[SonamB]

(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]

@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]

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]

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

[Shelley]

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.

[MFelix]

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

[Shelley]

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.

[MFelix]

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