Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

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

22 REPLIES 22

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.