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

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.

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:

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.

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

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.

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

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

• 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.
• Finally, click OK.

• Finally, rename the 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.

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

• Subsequently, the tables appear in Total Sales tab.

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

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

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.

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

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

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

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