13
Excel Power Query is a powerful add-in for Microsoft Excel that helps you import data from various sources.
Install Excel Power Query: How to do it
Microsoft Power Query is a powerful tool in Excel that helps you import, clean, and combine data from various sources—without tedious manual steps. Power Query is preinstalled in newer versions of Excel, but can also be installed later.
- To do this, open Excel and navigate to “File” > ‘Options’ > “Add-Ins.” Select “Microsoft Excel Add-Ins” from the drop-down menu and click “Next.”
- Check the box next to “Power Query for Excel” and confirm with “OK.” After a short loading process, Power Query is installed and ready to use.
- Power Query offers the ability to import data from various sources such as text files, websites, databases, or cloud services. To do this, click on the “Power Query” icon in the menu bar or select “Data” > ‘Query’ > “From File” or another source.
- In the window that opens, you can select the desired file or source and load the data. Power Query automatically recognizes the file format and imports the data into the editor.
- In the Power Query editor, you can now transform and clean the imported data as you wish. For example, delete unnecessary columns, replace values, or add new columns.
Using Excel Power Query
In Excel Power Query, all steps are stored in a query stack. They can be edited at any time.
- Position the cursor on the Excel table and select Data > Extract and Transform Data > From Table/Range. Excel opens the Power Query Editor and your data is displayed in a preview window.
- To view all query tables in the workbook in the query pane, click the arrow to the left of the preview pane.
- Once you have finished the transformations, load the data into a new Excel table or an existing worksheet.
- If you have created a query that needs to be updated regularly, you can schedule and share it. To do this, select “Query” > “Schedule Query Update” and set the interval and time.
- If you want to share the query with others, click “Share Query” and send a link to your colleagues. They can then open and use the query in their Excel.
Clean and transform data
The Power Query Editor provides you with numerous tools to clean your data and prepare it optimally for analysis. The most important functions include:
- Split columns: Split text values into multiple columns using separators (e.g., commas or spaces).
- Change data types: Assign the correct data type to columns—such as “date,” “text,” or “decimal”—to perform calculations and filters correctly.
- Remove duplicate values: Clean up your data with one click via “Start” > “Remove rows” > “Remove duplicates”.
Common errors and their solutions
Errors can occur, especially when working with external data sources. Some typical problems and solutions are:
- Missing connection to the data source: Check whether the file has been moved or renamed. If necessary, update the file path via “Data Source Settings”.
- Loading problems or missing permissions: Make sure you have the correct login information, especially for databases or cloud services.
- Outdated data: Update your queries regularly via “Refresh” or enable automatic updating when opening the workbook.
Advantages of Power Query over classic Excel functions
Power Query revolutionizes the way you work with data in Excel. Unlike manual methods using formulas, filters, or search and replace, it enables you to:
- Automation: Once created, queries can be reused again and again – ideal for monthly reports or regular data imports.
- Time savings: Power Query completes complex tasks such as combining multiple tables or removing duplicate entries in seconds.
- Transparency: Every step is documented and can be adjusted or undone in the editor.
- Reliability: Because Power Query always applies the same rules, you avoid human error and ensure consistent results.
