Power Query Multiple Sources in One Query (2 Practical Methods)

Get FREE Advanced Excel Exercises with Solutions!

In this tutorial, we will demonstrate how to add multiple sources in one query with Power Query. We will import data from the local Excel workbook and OData feed using Power Query Editor. Finally, we will merge and append data from both sources to create a report following a few transformation and aggregation steps.

After producing the new query using Power Query, you can make changes to the multiple sources and the new report will automatically update itself. Thus, you don’t have to reimport and reprocess the whole bunch. Just a simple click updates the data for you.

Read the full article to also learn about Power Query records. Before that, see the overview image of this article below.

Overview of Power Query multiple sources in one query

Download Practice Workbook

You can download the practice workbook for free.

What is Power Query Record?

A Power Query Record is like a container that holds related information from different sources within a single query in Power Query. It lets you organize and work with data from various sources all together.

We use Power Query Record:

  • To merge information from different sources into one query, making it simpler to work with multiple data sets.
  • It reduces the need for separate queries for each source, making data integration and manipulation more organized.
  • Working within a single query enhances efficiency by allowing transformations and operations on combined data.

How to Use Multiple Data Sources in One Query with Power Query

In this article, we will show the details steps to use multiple data sources in one query. We will show two features: merge and append multiple data sources.

1. Merge Multiple Data Sources in One Query with Power Query

In this part, we will import data from the given file into an Excel workbook, introduce new rows to column headers, delete unwanted columns, and load the query to a worksheet. After importing data from the OData feed in the same way, we will apply the Merge feature to combine them. The Merge command is useful when you want to combine columns from one source and include them in another source.

Step 1: Import Data into Excel Workbook

  • Here, we 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. 1st we will connect the Excel workbook.

  • Initially, go to Data >> Get Data >> From File >> From Excel Workbook.

Clicking From Excel Workbook option in Get Data

  • Select the Excel file >> Import.

Selecting file from local computer

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

Loading sheet for Power Query multiple sources in one query

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.

Source option in Applied Steps for Power Query multiple sources in one query

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

Testing File path and file type

In this task, delete the first 3 rows and other columns except ProductID, ProductName, CategoryID, and QuantityPerUnit. To do so,

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

Removing other columns with Remove Columns

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

Clicking Remove Top Rows option in Remove Rows

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

Removing top 3 rows for Power Query multiple sources in one query

  • Click Close & Load to load the products query.

Accessing Close & Load option to load Power Query multiple sources in one query

Step 2: Import Order Data from OData Feed

In this step, we will explore data in Excel workbook from the Northwind OData feed at http://services.odata.org/Northwind/Northwind.svc. Afterward, we expand the Order_Details table, take out some columns, calculate line totals, modify an OrderDate, group rows by ProductID and Year, change the query name, and stop the query from downloading into the Excel workbook.

1st we will connect to OData Feed.

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

Selecting From OData Feed option in Get Data

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

Entering OData feed url in URL

  • Double-click the Orders table in the Navigator window.

Selecting Orders table from Display Options for Power Query multiple sources in one query

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

Expanding Order_Details column

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

Adding column using 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]

Naming column as Total in New Column name

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

Transforming OrderDate to Year

  • Named the column as Year.

Naming column as Year

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

Grouping Year and Order_Details columns

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

Entering new column name in the Group by dialog box

  • Finally, rename the Query as Total Sales.

Naming new query as Total Sales

Finally, we will combine the two created queries. You can choose Merge or Append options to do so.

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

Merge option in Query

  • 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.

Selecting primary and secondary tables

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

Selecting Organizational in Privacy levels

  • Subsequently, the tables appear in Total Sales tab.

Table appears in Total Sales

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

Expanding Total Sales in Year and Order_Details columns

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

Output of Power Query multiple sources in one query

2. Append Multiple Data Sources in One Query with Power Query

When you put together information from two sources, it’s like adding one question to another. The Append command lets you mix many tables, no matter how they’re set up. If a column is in one source but not the other, it will show as Null. This is why making the two views exactly the same was crucial from the start. Follow the steps to append:

Follow the 1st and 2nd steps of the previous section. After that, follow the below section.

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

Tapping Append option in the Query tab

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

Selecting table numbers in Append dialog

  • Click the Close & Load option for the Append1 query.

Loading appended tables in workbook using Close & Load

  • Consequently, the combined table appears.

Appended output of Power Query multiple sources in one query

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.

In summary, we have discussed two methods to combine multiple sources in one query with Power Query. Now, we can make your own reports combining different queries that will auto-update when you change anything in the sources. If you have any suggestions regarding this topic, let us know in the comment box. Thank you for reading.

<< Go Back to Power Query Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Yousuf Khan
Yousuf Khan

Yousuf Khan has been working with the ExcelDemy project for over one year. He has written 47+ articles for the ExcelDemy site. He also solved a lot of user problems with ExcelDemy Forum. He has interests in Excel & VBA, Desktop and mobile applications, and projects & templates. He completed his graduation and post-graduation in Information Technology from Jahangirnagar University. Currently, he works as a VBA & Excel Content Developer in ExcelDemy projects, writing unique and informative content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF