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

[Chihiro]

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]

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.