M Inner Join

Understanding M Inner Join

The M language, used in Power Query, provides a powerful function Table.Join to perform inner joins. An inner join combines rows from two tables based on a specified condition, typically when there is a match between the columns.

In the context of Power Query, this operation is essential for data transformation tasks where you need to merge datasets with shared keys.

Syntax of Table.Join in M

The basic syntax for using Table.Join in M is as follows:

• table1: The first table you want to join.
• key1: The column(s) in the first table to match on.
• table2: The second table you want to join.
• key2: The column(s) in the second table to match on.
• joinKind: (Optional) The type of join; for an inner join, this is usually omitted as it defaults to inner join.

Example of M Inner Join

Let's consider two tables, Sales and Customers. We want to combine these tables to find all sales transactions with matching customer information.

In this example, the resulting table will only include rows where the CustomerID in the Sales table matches the CustomerID in the Customers table. The output will be:

Use Cases for Inner Joins

Inner joins are particularly useful for:

• Filtering data to include only records with existing relationships.
• Combining datasets to enrich data analysis.
• Ensuring data integrity by matching keys across datasets.

By using inner joins in your Power Query workflows, you can effectively streamline your data processing tasks.