Combining data in Power Query

Data Transformation in Power BI

Khaled Choucri

Strategic Analyst - Trilogy

Why combine data?

Append Queries

  • Many separate files with similar data
  • Data is too large to store in one file

union.png

Merge Queries

  • Tables have relationships between one or more columns
  • Tables can be combined to make a "flat" structure

Diagram of a star schema with a fact surrounded by five dimensions

Data Transformation in Power BI

Appending data

  • Append query will add rows to your table
    • "Vertically" combining queries
    • Column names must be the same

quarters.png

  • Nulls will appear when there are different column names or number of columns

An illustration showing two tables being appended to form a combined dataset.

Data Transformation in Power BI

Merging data

  • Merging queries will add columns to your table
    • "Horizontally" combining queries
    • Join tables through unique identifiers or keys

relational keys in two tables to be able to merge them.png

Data Transformation in Power BI

Merging data: types of joins

Inner Join

Inner Join Returns matched rows only

Left Join

Left Join Returns matched rows and left table's rows

Right Join

Right Join Returns matched rows and right table's rows

Full Join

Full Join Returns matched rows and both tables' rows

Data Transformation in Power BI

Merging data: example of a left join

Left Join

Screenshot 2022-04-19 at 17.25.25.png

Data Transformation in Power BI

Merging data: example of an inner join

Inner Join

Screenshot 2022-04-19 at 22.41.11.png

Data Transformation in Power BI

Let's practice!

Data Transformation in Power BI

Preparing Video For Download...