PQ #4 – Importing Data Like a Pro: Connecting to Excel, CSVs, and More
POWER QUERY
Power Query for Finance Professionals: From Zero to Hero
Welcome back to the Power Query for Finance Professionals: From Zero to Hero series! This series is designed to take you from a complete beginner to a Power Query expert, helping you automate data preparation and improve efficiency in your financial workflows.
If you haven’t already, check out the previous post in this series – they are available in the Related Articles section at the end of this article.
Introduction: The Power of Seamless Data Import
As a finance professional, you often work with data from multiple sources—Excel workbooks, CSV exports from accounting systems, or even entire folders of reports. Manually copying and pasting this data into Excel is not only inefficient but also prone to errors.
What if you could automate data imports with just a few clicks? That’s where Power Query comes in. It allows you to connect directly to multiple data sources, saving time and ensuring data accuracy.
In this guide, you’ll learn how to:
Import data from Excel workbooks
Import data from Excel table
Import data from Excel range
Connect to CSV files
Load multiple files from a folder
By the end of this post, you’ll be able to seamlessly pull data from various sources into Power Query, setting the stage for powerful data transformations.
The Problem: An Excel Data Challenge
Imagine you receive monthly sales reports in different formats - an Excel file from the sales team, a CSV export from your accounting software, and various files stored in a shared folder.
Your typical workflow involves:
✅ Manually opening each file
✅ Copy-pasting data into a master Excel sheet
✅ Fixing formatting inconsistencies
✅ Checking for missing or duplicate values
This time-consuming process introduces errors and wastes valuable hours every month. Instead of manually handling data, Power Query allows you to automate the entire import process in a few clicks.
Before diving into Power Query, here’s what you need:
✅ Excel 2016 or later (Power Query is built-in)
✅ A dataset to practice on (e.g., a messy sales or financial report)
✅ Basic knowledge of Excel (no coding required!)
Importing Data from Different Sources
Importing an Excel Worksheet
Go to the Data Tab in Excel.
Click on Get Data > From File > From Excel Workbook.
Select the Excel file you want to connect to.
Choose the worksheet containing the data.
Click Transform Data to open the Power Query Editor.
Tip: This method is the least favorable to import data.
Importing an Excel Table from Another Excel File
Ensure your data is stored as a Table (Select your range and press Ctrl + T to create a table).
Go to the Data Tab in Excel.
Click on Get Data > From File > From Excel Workbook.
Select the Excel file you want to connect to.
Choose the table containing the data.
Click Transform Data to open the Power Query Editor.
Tip: You can also import an Excel Table from your current Excel File.
Tip 2: This is a preferred method of importing data as a table cannot be altered.
Importing a Range from the Current Excel File
In your current Excel File, define a Named Range (Select the range, go to Formulas > Name Manager, and create a name for the range).
Go to the Data Tab in Excel.
Click on Get Data > From Other Sources > Blank Query.
In the Formula Bar, type Excel.CurrentWorkbook().
Under the column Content, click on the table corresponding to your range.
Tip: Using named ranges makes it easier to reference specific portions of your data without changing the structure of your file.
Importing a CSV File
Go to the Data Tab in Excel.
Click on Get Data > From File > From Text/CSV.
Select the CSV file you want to connect to.
Excel will automatically detect column delimiters.
Click Transform Data to open the Power Query Editor.
Tip: Ensure your CSV files have consistent column headers to avoid transformation issues.
Importing Multiple Files from a Folder
Go to the Data Tab in Excel.
Click on Get Data > From File > From Folder.
Select the folder containing the files.
Power Query will display all files in that folder.
Click Combine & Transform to merge them into a single dataset.
Tip: Use consistent file naming conventions to ensure smooth automation.
For a step-by-step visual walkthrough of each importing method, check out the YouTube video below.
The Outcome: Automating Data Imports for Efficiency
By leveraging Power Query’s data import capabilities, you can:
✅ Save Time – Eliminate repetitive copy-pasting tasks
✅ Reduce Errors – Automate data handling for greater accuracy
✅ Ensure Data Consistency – Standardize imports across different sources
✅ Automate Monthly Reporting – Set up once and refresh data with a single click
With Power Query, data integration becomes fast, efficient, and scalable, allowing you to focus on analysis instead of data wrangling.
Conclusion: Data Importing is the First Step to Data Transformation
We covered a lot in this post:
✅ How to connect to Excel workbooks, tables, and named ranges
✅ How to import CSV files
✅ How to load multiple files from a folder
✅ Why automating data imports improves efficiency and accuracy
If you’re a finance professional looking to streamline your data workflow, mastering Power Query’s import functions is a game-changer.
Next Steps: Get Hands-On with Power Query
Now that you know how to import data into Power Query, here’s what you can do next:
📌 Try importing data from different sources using the steps above – Use the demo files available at https://github.com/versatilebieu/power_query/tree/main
📌 Stay tuned for my next post: Understanding the Applied Steps Pane – The Key to Data Transformation
