Power Query Problems

Create an Automatically Updated Course Catalog with Power Query in Excel

Amos Gikunda Sep 27, 2026 869 views

Question details

The user needs to consolidate course data from five different student information systems into one searchable, filterable, and automatically refreshing Excel workbook.

How to Create an Automatically Updated Course Catalog using Power Query

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating an automated master course catalog by connecting to multiple backend student systems and merging the data.
Observed behavior: The user requires a centralized workbook where combined course data can be updated regularly and shared safely without exposing raw backend tables or sensitive information.

Before you start

Ensure you have the proper credentials and access rights to the five source files or database systems. Prepare and use sanitized sample data for testing, making sure to remove all confidential, private, and personally identifiable information (PII) before sharing the workbook.

Solution 1Recommended

Use Power Query to Append Data from Multiple Sources

Power Query allows you to connect to multiple data systems, filter out sensitive information, and merge or append the data into a single table that refreshes automatically.

Power Query is the ideal tool for this task because it records your data transformation steps. You only need to set up the connection and cleaning rules once, and Excel will automatically apply them whenever you refresh the data.

Connect to Source Systems

Open Excel, navigate to the 'Data' tab, and click 'Get Data'. Select your data source type (such as 'From File' > 'From Workbook' or 'From Database') and establish a connection to each of the five student information systems.

Sanitize and Transform Data

In the Power Query Editor, remove any columns containing sensitive or personally identifiable information (PII). Rename columns to ensure consistency across all five queries (e.g., standardizing 'CourseID' and 'Course_Name').

Append the Queries

On the Home tab of the Power Query Editor, click 'Append Queries' (or 'Append Queries as New'). Select all five tables to stack them on top of each other, creating a single, unified master catalog.

Load and Configure Auto-Refresh

Click 'Close & Load To...' and choose to load the data as a Table or PivotTable Report in your workbook. Right-click the newly created query in the 'Queries & Connections' pane, select 'Properties', and check 'Refresh data when opening the file' or set a background refresh interval.

Use Power Query to Append Data from Multiple Sources

Data Safety: By removing PII during the transformation step, the final Excel Table loaded into the worksheet is safe to share, as the underlying private data is never exposed in the front end.

Free Microsoft Office alternative

Try WPS Office for Seamless Data Management

While Power Query is a specific feature within Microsoft Excel, WPS Office provides a powerful, lightweight, and completely free alternative for managing complex datasets. WPS Spreadsheet is highly compatible with Microsoft Excel formats, ensuring you can open, view, and analyze extensive workbooks without the need for an expensive subscription.

1. Download and Install: Visit the official WPS website to download the free version of WPS Office and follow the straightforward installation instructions.
2. Open Your Workbook: Launch WPS Spreadsheet and click 'Open' to import your existing data files or Excel workbooks.
3. Analyze and Share: Utilize built-in data sorting and filtering features to search through your course catalog, and easily share the file with your team.

Highly compatible with Microsoft Excel (.xlsx, .xls) and standard CSV filesLightweight application that runs smoothly across Windows, Mac, and mobile devicesRobust data filtering, sorting, and PivotTable capabilities for catalog managementFamiliar and intuitive user interface requires no additional learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Can I share a Power Query workbook with users who do not have access to the original databases?

Yes. If you load the transformed data into an Excel Table and share the workbook, other users can view, filter, and search the existing data. However, they will not be able to refresh the data to see the latest updates unless they also have access and credentials for the source systems.

How do I ensure sensitive student information is permanently removed from the catalog?

Inside the Power Query Editor, explicitly select the columns containing sensitive data (like names or IDs) and delete them before loading the data into Excel. The deleted columns will not be transferred to the final workbook, ensuring the shared file remains secure.

Why is my consolidated course table not updating automatically?

By default, external data connections require a manual refresh. To automate this, right-click the query in the 'Queries & Connections' pane, select 'Properties', and check the box for 'Refresh data when opening the file' or configure a specific time interval under 'Refresh every X minutes'.

Excel & Data Problems