If column contains values from column in another table...

[csmathers]

I have a table Person, and person table has a stateId field for birthplace and current address ('Id','BirthPlaceState','CurrentAddressState').  In SQL both fields are Foreign keys to the LookupTable with has ('Id','PersonFK','Value').  How can I get the state Value from the Lookup Table for both the birthplace State and the current address state?  

[Greg_Deckler]

You could do this several ways. You could import your other tables and form a relationship between your tables. You could also write your SQL such that you create a SQL statement that combines all of the tables together (join). You could also use Power Query to Merge all of your tables together (join).

[csmathers]

Thanks for the response, there is a relationship in sql server but Power Bi does not maintain the relationship because it only allows one related column from person to Lookup table.  If I try to add the 2nd relationship it gives me an error and says I can only have one.  

Yeah, I have a sql query to do it but I wanted give my power users the ability to add those columns using the fields selector.  I don't want to have to provide a sql query any time they want to add a query.  

What is Power Query? 

[v-ljerr-msft · Accepted solution]

Hi @csmathers,

Following are the detail steps to use Merge Queries in Power Query in your scenario.

I assume you have the tables like below.

Person

State

1. Click "Edit Queries" in Home tab.

2. Select "Person" on Queries, and click Merge Queries.

3. Select "BirthPlaceState" column for "Person" table, and select "State" table, "Id" column, click "OK".

4. Click the double arrow and select "Value", then click "OK".

5. Rename the added column.

6. Repeat step 2, 3, 4, 5 for "CurrentAddressState". Last, click "Close&Apply".

Then you should be able to get a new "Person" table with state values of "BirthAddressState" and "CurrentAddressState".

Regards

[csmathers]

That is just what I was looking for!!! Thanks so much!