Skip to content

10. Merge Tables (Lookup)

Goal: Match the city population data to the location table. Use a merge (join) for this.

A merge is the Power Query name for a lookup. You match one column in table A with one column in table B. Power BI pulls the values across.

Lookup vs merge

  • Excel: VLOOKUP or XLOOKUP. You type a formula in a cell.
  • Power Query: Merge. You choose the two tables and the matching column. Power Query does the rest.

Step 1: Start the merge

  1. Click the Location table in the Queries panel.
  2. Click Home > Merge Queries.
  3. Do not choose "Merge Queries as New". Change the Location table itself.

Merge Queries window The Merge Queries window with the matching column selected.

Step 2: Select the matching columns

  1. In the top table, click the City column.
  2. In the bottom list, select the CA Population table.
  3. In the bottom table, click the matching column.
  4. Both column names must be selected. A match preview shows below.

Column selection is important

If you select the wrong column, the match preview is empty. Click the correct column before you continue.

Step 3: Choose the join kind

Join kind What it does
Left Outer Keep all rows from the first table. Add matches from the second.
Inner Keep only rows that match in both tables.
Full Outer Keep all rows from both tables.
  1. For a lookup, choose Left Outer.
  2. Keep Fuzzy Matching off. Turn it on only for typo problems.

The trainer uses Left Outer.

Step 4: Expand the new column

  1. Click OK. A new column appears. Its name starts with the table name.
  2. Click the expand icon (two arrows) on the column header.
  3. Uncheck the columns you do not need.
  4. Check Population only.
  5. Click OK.

Step 5: Replace the old population column

  1. Right-click the old Population column.
  2. Click Remove.
  3. Rename Population.1 to Population.

Now the Location table has the newest population values.

Two merge steps

Merge has two steps: first select the matching column, then expand the columns you want. Do not skip the expand step.

Step 6: Check for errors

  1. Turn on Column Profile to see errors fast.
  2. Find the error rows. In this data, the city "Burbank" has two names: Burbank CT and Burbank CDP. One of them does not match.
  3. Fix the error rows. You have three choices:
  4. Filter out the error rows.
  5. Remove the errors.
  6. Replace the errors with a text value.

The trainer replaces the errors with the text No data.

Next: Continue to 11. Conditional Columns.