Category: Power Query

Data Analysis, Data Development, Data Integration, Data Munging, Data Wrangling, Excel, Power BI, Power Query, SQL

Merging Two Excel Files or Sheets Using Power Query (with the merge based on Multiple Columns)

Excel Power Query is a powerful, no-code/low-code tool that allows you to combine and transform data from multiple sources in a repeatable and refreshable way. One common use case is merging two Excel files or worksheets based on multiple matching columns, similar to a SQL join. Power Query is a major part of Power BI, but it can be used in Excel.

When to Use Power Query for Merging

Power Query is ideal when:

  • You receive recurring Excel files with the same structure
  • You need a reliable, refreshable merge process
  • You want to avoid complex formulas like VLOOKUP or XLOOKUP across many columns

Step-by-Step Overview

1. Load Both Data Sources into Power Query

  • Open Excel and go to Data → Get Data
  • Choose From Workbook (for separate files) or From Table/Range (for sheets in the same file)

Tip: Ensure the columns you plan to merge on have the same data types (e.g., text vs. number).

  • Load each dataset into Power Query as a separate query

2. Start the Merge Operation

  • In Power Query, select the primary table
  • Go to Query → Merge Queries
  • Choose the secondary table from the dropdown

3. Select Multiple Matching Columns

  • Click the first matching column in the primary table
  • Hold Ctrl (or Cmd on Mac) and select additional matching columns
  • Repeat the same column selections in the secondary table, in the same order

For example, if you needed to perform the merge on CustomerID, OrderDate, and Region, you would click Customer ID, then hold the Ctrl key and click OrderDate, then (while still holding down the Ctrl key) click Region.

Power Query treats this as a composite key, and all selected columns must match for rows from both tables to merge.

4. Choose the Join Type

Select the appropriate join kind:

  • Left Outer – Keep all rows from the first table (most common) and brings in the values for the matching rows from the second table
  • Inner – Keep only matching rows from both tables
  • Full Outer – Keep all rows from both tables, merging the table where there is a match and having just the values from the respective tables when there is no match

Click OK to complete the merge.

5. Expand the Merged Data

  • A new column appears containing nested tables
  • Click the expand icon to select which columns to bring in
  • Remove unnecessary columns to keep the dataset clean

6. Load and Refresh

  • Click Close & Load
  • The merged dataset is now available in Excel
  • When source files change, simply click Refresh to update everything automatically

Key Benefits

  • Handles multi-column joins cleanly and reliably
  • Eliminates fragile lookup formulas
  • Fully refreshable and auditable
  • Scales well as data volume grows

In Summary

Using Power Query to merge Excel data on multiple columns brings database-style joins into Excel, making your workflows more robust, maintainable, and professional. Once set up, it saves time and reduces errors—especially for recurring reporting and analytics tasks.

Thanks for reading!

Power BI, Power Query

How to update your Power BI source file location

The location of your source files has changed, and now you need to update your Power BI report to use the new location. To update the directory or location of your source file, in Power BI Desktop, click Transform Data -> Data Source Settings 

Then click on the entry that corresponds to the path you need to update.

Update or entirely change the path and click ok. Apply your changes.

It becomes a little more complicated when you are changing a local folder to a SharePoint location, which we will cover in another post, but for changing location of single files, it’s that simple. 

Thanks for reading!

Business Intelligence, Business Intelligence Platform, Data Cleaning, Data Integration, Power BI, Power Query

Power BI load error: load was cancelled by error in loading a previous table

You may run into this error when loading Power BI:

"load was cancelled by error in loading a previous table"

If you do get this error, keep scrolling down to see what the “inducing” error is. This message is an indication that there was an error previous to getting to the current table in the process. The real, initial error will be more descriptive. Start with resolving that error(s), and then this one will go away.

I hope you found this helpful.

Business Intelligence, Business Intelligence Platform, Power BI, Power Query

Power BI refresh error: Column ‘X’ in table ‘Y’ contains blank values and this is not allowed for columns on the one-side of a many-to-one relationship or for columns that are used as the primary key of a table

I was getting this error message when I attempted to refresh a Power BI application: 

"Column 'Date' in table 'Date Dim' contains blank values and this is not allowed for columns on the one-side of a many-to-one relationship or for columns that are used as the primary key of a table"

However, despite what the message indicated, I double-checked and confirmed that I did not have any blank values in the ‘Date Dim’ table.

It turns out that you may also get this error (although incorrectly worded in my opinion) if the blanks are in the joining table. In my case, I had blanks in a ‘Snapshot Date’ column in the fact table that was joined to the ‘Date Dim’ table. Once these blanks were filled, the refresh ran without error.

One thing to look out for in these cases (since this is what happened in my case), if your source is Excel, undo all filters to make sure that you do not have any rows being filtered out when checking for blanks values across your columns, because this could potentially inadvertently hide the rows with the blank values and cause you to miss them.

I hope you found this helpful.

Business Intelligence (BI) Development, Data Cleaning, Data Development, Excel, Power BI, Power Query

Creating a DATE value in Power BI DAX, Power Query M, and Excel

You may at times need to create a date value in Power BI either using DAX or M, or in Excel. This is a quick post that describes how to create a date value in Power BI DAX, Power Query M language, and in Excel. Working with dates is an every-day thing for anyone that works with data.

In Power BI DAX, the syntax is:

DATE(<year>, <month>, <day>) //the parameters must be valid numbers

DATE(2025, 8, 23) //returns August 23, 2025

In Power Query M, the syntax is:

#date(<year>, <month>, <day>) //the parameters must be valid numbers

#date(2022, 3, 6) //returns March 6, 2022

In Excel, the syntax is:

DATE(<year>, <month>, <day>) //the parameters must be valid numbers

DATE(1989, 12, 3) //produces 12/3/1989 (officially returns a number that represents the date in Excel date-time code)

Thanks for reading. Hope you found this useful.