Power Query Multiple Sources in One Query: 2 Practical Methods

Method 1 – Merge Multiple Data Sources in One Query with Power Query

Step 1: Import Data into Excel Workbook

Import an Excel workbook from the Source query using the following formula:

=Excel.Workbook(File.Contents("C:\Power_Query_Multiple_Sources_in_One_Query.xlsx"), null, true)

This formula will automatically generate after following the steps below. Connect the Excel workbook.

Go to Data >> Get Data >> From File >> From Excel Workbook.

Select the Excel file >> Import.

In the Navigator dialog, select the Merge sheet >> Load.

When you use Power Query, it adds some steps automatically for your convenience. See the Applied Steps option in the Query Settings to explore each step and learn more about them.

Right-click on Source >> click on Edit Settings.

Test the File Path and Open file as boxes >> OK.

Delete the first 3 rows and other columns except ProductID, ProductName, CategoryID, and QuantityPerUnit.

Select the columns you want to keep pressing Ctrl + select columns >> Remove Columns >> Remove Other Columns.

To remove the rows, click Remove Rows >> Remove Top Rows.

Put 3 in the Number of rows option >> OK.

Click Close & Load to load the product query.

Step 2: Import Order Data from OData Feed

Connect to OData Feed.

Go to Data >> Get Data >> From Other Sources >> From OData Feed.

Copy and paste the given URL in URL box >> OK.

Double-click the Orders table in the Navigator window.

Click on the Expand button of Order_Details >> check ProductID, UnitPrice, Quantity >> OK.

Remove unwanted columns as shown previously.
Click the Table icon of OrderDate >> Add Custom Column.

In Custom Column dialog, write Total in the New Column name box >> type the following formula in the Custom column formula box >> OK.

=[Order_Details.UnitPrice] * [Order_Details.Quantity]

Right-click on OrderDate >> Transform >> Year >> Year.

Named the column as Year.

Select Year and ProductID columns >> right-click on them >> Group By.

In Group By dialog, insert Total Sales in New column name, Sum in Operation, Total in Column box.
Click OK.

Rename the Query as Total Sales.

We will combine the two created queries. Choose the Merge or Append options to do so.

Double-click on Merge query >> go to Query tab >> Merge.

In Merge dialog, select Merge as primary table.
Select ProductID table as common column for both tables.
Set Total Sales as secondary table.
Select ProductID column and press OK.

Choose Organizational in the Privacy levels dialog to secure the workbook >> Save.

The tables appear in Total Sales tab.

Click the Expand button of Total Sales >> check Year and ProductID >> OK.

Click Close & Load to load the table in your workbook.

Method 2 – Append Multiple Data Sources in One Query with Power Query

Double-click on Merge query >> Query >> Append.

In the Append dialog box, select Two tables as we have 2 queries.
Set Merge as First table and Total Sales as Second table.
Press OK.

Click the Close & Load option for the Append1 query.

The combined table appears.

Things to Remember

Privacy Levels stop the accidental mixing of data from different sources, some of which might be private. A query could accidentally share private data with a harmful source. Power Query checks each source and puts it on a privacy level: Public, Organizational, or Private.
With Power Query, you can open up tables connected through a column, then combine the columns of the linked table before expanding the information in the main table.

Frequently Asked Questions

1. How do I replace multiple texts or characters in Power Query?
When inside the Power Query Editor, we can perform a Replace Values step from either the Transform tab or the Right Click menu. Select the column where we want to replace values >> go to the Transform tab >> press the Replace Values command.

2. How many records can Power Query handle?
Power query can handle 1,048,576 records.

3. Can I merge data from different file types in a single query?
Yes, Power Query supports combining data from various file types like Excel, CSV, databases, and more into a single query.

4. What if the data structures are different in each source?
Power Query provides tools to transform and shape data, so you can align and reshape different data structures before combining them.