The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
Solved! Go to 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
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).
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?
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
That is just what I was looking for!!! Thanks so much!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
94 | |
81 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |