Category: SQL

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!

Analytics, Business Intelligence (BI) Development, Data Analysis, Data Development, Data Integration, Data Modeling, Data Munging, Data Wrangling, Power BI, SQL

Understanding UNION, INTERSECT, and EXCEPT in Power BI DAX

When working with data in Power BI, it’s common to need to combine, compare, or filter tables based on their rows. DAX provides three powerful table / set functions for this: UNION, INTERSECT, and EXCEPT.

These functions are especially useful in advanced calculations, comparative analysis, and custom table creation in reports. If you have used these functions in SQL, the concepts here will be familiar.

Sample Dataset

We’ll use the following two tables throughout our examples:

Table: Sales_2024

The above table (Sales_2024) was created using the following DAX code utilizing the DATATABLE function (or you could enter the data directly using the Enter Data feature in Power BI):

Table: Sales_2025

The above table (Sales_2025) was created using the following DAX code utilizing the DATATABLE function (or you could enter the data directly using the Enter Data feature in Power BI):

Now that we have our two test tables, we can now use them to explore the 3 table / set functions – Union, Intersect, and Except.

1. UNION – Combine Rows from Multiple Tables

The UNION function returns all rows from both tables, including duplicates. It requires the same number of columns and compatible data types in corresponding columns in the the tables being UNION’ed. The column names do not have to match, but the number of columns and datatypes need to match.

DAX Syntax:

UNION(<Table1>, <Table2>)

For our example, here is the syntax and resulting dataset:

UnionTable = UNION(Sales_2024, Sales_2025)

As you can see, the UNION returns all rows from both tables, including duplicates.

If you were to reverse the order of the tables (in the function call), the result remains the same (as shown below):

To remove duplicates, you can wrap the UNION inside a DISTINCT() function call, as shown below:

2. INTERSECT – Returns Rows Present in Both Tables

The INTERSECT function returns only the rows that appear in both tables (based on exact matches across all columns).

DAX Syntax:

INTERSECT(<Table1>, <Table2>)

For our example, here is the syntax and resulting dataset:

IntersectTable = INTERSECT(Sales_2024, Sales_2025)

Only the rows in Sales_2024 that are also found in Sales_2025 are returned.

If you were to reverse the order of the tables, you would get the following result:

IntersectTableReverse = INTERSECT(Sales_2025, Sales_2024)

In this case, it returns only the rows in Sales_2025 that are also found in Sales_2024. Since the record with “D – West – $180” exists twice in Sales_2025, and also exists in Sales_2024, then both records are returned. So, while it might not be relevant for all datasets, order does matter when using INTERSECT.

3. EXCEPT – Returns Rows in One Table but Not the Other

The EXCEPT function returns rows from the first table that do not exist in the second.

DAX Syntax:

EXCEPT(<Table1>, <Table2>)

For our example, here is the syntax and resulting dataset:

ExceptTable = EXCEPT(Sales_2024, Sales_2025)

Only the rows in Sales_2024 that are not in Sales_2025 are returned.

If you were to reverse the order of the tables, you would get the following result:

ExceptTableReverse = EXCEPT(Sales_2025, Sales_2024)

Only the rows in Sales_2025 that are not in Sales_2024 are returned. Therefore, as you have seen, since it pulls data from the first table that does not exist in the second, order does matter when using EXCEPT.

Comparison table summarizing the 3 functions:

FunctionUNIONINTERSECTEXCEPT
Purpose & OutputReturns all rows from both tablesReturns rows that appear in both tables (i.e., rows that match across all columns in both tables)Returns rows from the first table that do not exist in the second
Match CriteriaColumn position (number of columns) and datatypesColumn position (number of columns) and datatypes and valuesColumn position (number of columns) and datatypes must match and values must not match
Order Sensitivityorder does not matterorder matters if you want duplicates returned when they exist in the first tableorder matters
Duplicate HandlingKeeps duplicates. They can be removed by using DISTINCT()Returns duplicates only if they exist in the first tableReturns duplicates only if they exist in the first table

Additional Notes for your consideration:

  • Column Names: Only the column names from the first table are kept; the second table’s columns must match in count and data type.
  • Performance: On large datasets, these functions can be expensive, so you should consider filtering the data before using them.
  • Case Sensitivity: String comparisons are generally case-insensitive in DAX.
  • Real-World Use Cases:
    • UNION – Combining a historical dataset and a current dataset for analysis.
    • INTERSECT – Finding products sold in both years.
    • EXCEPT – Identifying products discontinued or newly introduced.

Thanks for reading!

Data Munging, Data Warehousing, Data Wrangling, SQL

SQL Tips: How to generate insert statements using the data from the output of a select statement using Toad

If you need to build insert statements for many rows of data for inserting that data into another table or into the same table in a different environment, there is a convenient way to do this in Toad. This is often needed to move data around.

In Toad, execute the appropriate select statement on the source table …

Select * from [my_schema].[my_simple_table];

I used a simple “select *” above, but your SQL statement can be any valid SQL statement that returns the data you want to insert into the other table. You may add specific columns, add filters, joins, and any other valid SQL operation.

Let’s say you want to insert the output into another table in a different schema.

Right-click on the output result data, and click “Export Dataset…”

From the “Export format” drop down menu, choose “Insert Statements”

In the Output section of the Export Dataset dialog box, enter the location and name of the Insert Script file that will be generated.

There are several other parameters that you could choose but we won’t cover them all here.

If you only wanted to generate inserts for some selected rows, select “Export only selected rows”.

If you need to specify the schema of the target table, select “Include schema name”

In the Table section, enter the name of the target schema and table

Note, there are data masking options available that can be very useful if, for example, you are moving some data from a Production environment to a Non-Production environment, and you do not want to expose the data there.

After you have set the parameters relevant to your scenario, Click “OK”.

The Insert Script file of all the data will be generated with the filename and at the location you specified. And the insert statements will include the name of the schema and table you specified.

Thanks for reading!

Data Development, Database Administration, Databases, SQL

SQL Tips: How to rename a column in a table – Oracle database – Oracle SQL

At times you will need to change the name of a column in an existing table. If you are not changing the data type, it is just one statement / step that needs to be executed. However, I strongly recommend that you also do a backup step, especially if you’re making the change in a production environment, just in case of an unexpected issue.

If you choose to do the backup, you may perform this with a “create-table-as-select” statement in this form:

create table [table_name_backup] as select * from [table_name];

Here is an example of the above statement:

create table EMPLOYEES_BKUP as select * from EMPLOYEES;

Now that the table you are modifying is all backed up, you can proceed to rename the column.

The rename SQL statement would take this form:

alter table [table_name] rename column [existing_column_name] to [new_column_name];

An example of the statement:

alter table EMPLOYEES rename column SEX to GENDER;

Thanks for reading! I hope you found this information useful.

Data Development, SQL

SQL tips: Get last day and first day of month – Oracle SQL

At times you may need to dynamically determine the first or last day of a month based on the current date or some date in your data. This post provides a few options for deriving the First day of month, and Last day of month. And also, it provides a couple tweaks to get Last day of the next or previous month.

Get the current date (today’s date):
select sysdate from dual;

SYSDATE

16-MAY-22

Get the last day of the current month:
select trunc(last_day(sysdate)) as LastDayOfMonth from dual;

LASTDAYOFMONTH

31-MAY-22

Side note: The last_day() function can also be useful for dynamically determining leap years (that is, whether February has 28 or 29 days)
select
last_day(date ‘2020-02-01’) LastDayOfFeb2020, — leap year
last_day(date ‘2021-02-01’) LastDayOfFeb2021 — not a leap year
from dual;

LASTDAYOFFEB2020 LASTDAYOFFEB2021

29-FEB-20 28-FEB-21

Get the last day of the next month:
select add_months(trunc(last_day(sysdate)), 1) as LastDayOfNextMonth from dual;

LASTDAYOFNEXTMONTH

30-JUN-22

Get the last day of the previous month:
select add_months(trunc(last_day(sysdate)), -1) as LastDayOfPreviousMonth from dual;

LASTDAYOFPREVIOUSMONTH

30-APR-22

Get the first day of the current month:

select trunc(sysdate,’month’) as FirstDayOfMonth from dual;
select trunc(sysdate,’MM’) as FirstDayOfMonth from dual;

FIRSTDAYOFMONTH

01-MAY-22

Get the first day of the next month:

select add_months(trunc(sysdate,’MM’), 1) as FirstDayOfNextMonth from dual;

FIRSTDAYOFNEXTMONTH

01-JUN-22

You can also get the first day of the next month using this:
select trunc(last_day(sysdate)+1) as FirstDayOfNextMonth from dual;

FIRSTDAYOFNEXTMONTH

01-JUN-22

Get the first day of the previous month:

select add_months(trunc(sysdate,’MM’), -1) as FirstDayOfPreviousMonth from dual;

FIRSTDAYOFPREVIOUSMONTH

01-APR-22

Here are 3 resources for getting the first and last day of the month values on a SQL Server database:

https://zarez.net/?p=2462

SQL SERVER – Simple Method to Find FIRST and LAST Day of Current Date
SQL Server EOMONTH Function

Thanks for reading! I hope you found this information useful!

Good luck on your data journey.

Data Development, SQL

SQL Tips: How to replace a character or a part of a string in a column value – Oracle SQL

If you frequently work with data, you will occasionally need to replace a part of the values in a column for some reason. In our case, we recently had some data that was loaded into a table from a file source and all the values had an unwanted leading and trailing tab character.

We needed to remove these tabs and preferably without resourcing and reloading the data. The solution was to use the REPLACE SQL function.

This is the form of the UPDATE statement that uses the REPLACE function

Update TABLENAME Set COLUMN_TO_BE_UPDATED = REPLACE(COLUMN_TO_BE_UPDATED, ‘Char_or_String_to_replace_in_column_values’, ‘Char_or_String_to_replace_it_with’);

Here is an example that replaces the long street names in the ADDRESS column with their abbreviated corresponding name:

update EMPLOYEE set ADDRESS = REPLACE(ADDRESS, ‘Street’, ‘St’ ), ADDRESS = REPLACE(ADDRESS, ‘Avenue’, ‘Ave’ ), ADDRESS = REPLACE(CITY, ‘Circle’, ‘Cir’ ) where COUNTRY = ‘United States’;

To solve the issue we had, the SQL statement below was used to update the EMPLOYEE column values by replacing the tab character with NULL – as shown below.

Important Note: Before making any major updates to a table, it’s a best practice to back up the table first.

update EMPLOYEE_EXPENSE_DATA set EMPLOYEE = REPLACE(EMPLOYEE, ‘    ‘, NULL) where EMPLOYEE like ‘  %’;

Important Note: Verify your update before you commit the changes.

We repeated this SQL for each column that had the “bad” (unwanted leading and trailing tabs) values.

Thanks for reading! I hope you found this information useful.

Data Development, SQL

SQL Tips: How to generate a delimited list from the values in an Oracle database table column

This is a quick post that shows how to generate the values from a table column in a delimited list format.

If you have a table named CONTACTS that has the following columns and values:

select * from CONTACTS;

And you wanted to generate a delimited list of the names in the NAMES column like this:

Adam, Jane, John, Lisa, Mark, Mary, Noah

Or like this:

Adam | Jane | John | Lisa | Mark | Mary | Noah

You would use SQL statements like these to generate the above output – note the delimiter in each case, and also note that you can sort the output:

— comma delimiter example …

SELECT listagg(NAME, ', ') within group (order by NAME) FROM CONTACTS;

Adam, Jane, John, Lisa, Mark, Mary, Noah

— pipe delimiter example …

SELECT listagg(NAME, ' | ') within group (order by NAME) FROM CONTACTS;

Adam | Jane | John | Lisa | Mark | Mary | Noah

You can find more information about the listagg function here.

Thanks for reading. I hope you found this useful.