Forum Discussion
Post ProdigyHow 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. : )
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
22 Replies
v-jiascu-msftMicrosoft Employee
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- 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!!
- 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:
TableWhat the heck am I doing wrong? Thanks for your help!
arnabmitHelper I
I am too getting same error. Any solution?
- ThomasWeppler
Impactful Individual
Great post.
- MFelix
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- 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.
- Chihiro
Solution Sage
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]
- 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.
