Forum Discussion
Post ProdigyHow 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]Best Regards,
Dale
Super UserYou 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
Post ProdigyThe 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]
- 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.
- MFelixHi 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.
- MFelixHi 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
