cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Shelley Member
Member

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

Accepted Solutions
Community Support Team
Community Support Team

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

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.
11 REPLIES 11
Super User
Super User

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

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Chihiro Established Member
Established Member

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

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.

Shelley Member
Member

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

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 Established Member
Established Member

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

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]

Super User
Super User

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

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Shelley Member
Member

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

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. 

Shelley Member
Member

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

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.

Super User
Super User

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

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Community Support Team
Community Support Team

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

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.