Merging Queries
The Countries table has quite a few columns that we don’t need. Let’s remove them so it’s easier to see the data that we will work with.
Select the Countries table.
Hold down Ctrl + Select headings of columns:
- LastEditedBy
- ValidFrom
- ValidTo
- Application.People
Right click + Choose Remove Columns.
- LastEditedBy
The database has defined relationships between tables (called foreign keys).
Power Query has detected those relationships, and brought in the related tables, such as Application.StateProvinces. Let’s bring in some of the columns from Application.StateProvinces that we’ll want to use.
- Click on the Expand icon to expand column Application.StateProvinces.
- Unselect Use original column name as prefix.
- Select only StateProvinceName and StateProvinceID.
Now we’re going to combine Cities and Countries into one table as it is good data modeling practice (we’ll discuss this next module)
- Under Queries, select the Cities table.
- Go to Home Tab > Select Merge Queries > Click on Merge Queries
Verify that Cities is selected.
Use the middle dropdown menu to select Countries.
Select StateProvinceID in both tables (You need to have a matching key in order to merge between two tables)
Expand the new Countries column in the newly merged Cities table. Choose CountryName, Region and Subregion only.
Right click Countries query and unselect Enable load. We don’t need the data from this table loaded into the model because we have already merged in the columns that we want.
Power Query will warn you that any visuals you have attached to the Countries query will break. That’s fine.
The Countries table will not show up in Data model View because we have disabled the load in the steps above.