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.

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

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.

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 product query.

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

Step 2: Import Order Data from OData Feed

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

Entering new column name in the Group by dialog box

  • Rename the Query as Total Sales.

Naming new 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.

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

  • The tables appear in Total Sales tab.

Table appears in Total Sales

  • Click the 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

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

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

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

Download Practice Workbook

You can download the practice workbook for free.

<< Go Back to Power Query Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
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