In Microsoft Excel, the most significant errors typically occur during the data import phase. Data can look fine on the surface but still have hidden errors, such as corrupted date formats, missing special characters, or misaligned columns that can lead to systemic errors in your final reports. To maintain a reliable and repeatable workflow, avoid relying on Excel’s default Open file or drag and drop methods. Instead, use Power Query, a built-in tool designed specifically for connecting to and transforming data.

Why Power Query Matters?

Power Query turns messy, one-off data cleanup into a defined process. Instead of manually fixing columns every time a new file arrives, you build a pipeline that enforces rules automatically. E.g

• Set correct data types (text, number, date)
• Clean and reshape messy data
• Automate the process so it works the same way every time

This is important when working with external data sources like CSVs, websites, or databases. 

This guide covers three common data import scenarios and shows how to handle them using a structured approach. 

1.  Importing CSV/Text Files

Opening a CSV file directly in Excel often leads to:

• Dates being misinterpreted
• Leading zeros disappearing
• Special characters are displaying incorrectly

Best Practise:

Step 1: Importing the CSV/Text

• Go to the Data ribbon 
• Click on Get Data 
• From Files select Text/CSV

Step 2: Choose your file 

• A window will open
• Find and select your .csv or .txt file

Step 3: Adjust the settings (if needed)

Excel will try to guess these, but you can change them:

• File Origin (Encoding)
  This tells Excel how to read special characters (like accents or symbols).
  If unsure, leave it as the default 
• Delimiter
  This is the character that separates your data.
  Common ones are: Comma (,), Semicolon (;), Tab

Be sure to select the one best suited for separating your data.

• Data Type Detection

There are three options available, and understanding how each one works helps you avoid selecting a setting that could lead to errors in your worksheet. 

1.     Based on the first 200 rows (default)

  Excel looks at only the first 200 rows of your data to guess the data types.

  What this means:

• If the first 200 rows look correct, Excel assumes the rest is the same
• It’s faster because it doesn’t scan the entire dataset

This works in some cases, depending on the data you have, but the problem with this option is that, if rows after the first 200 are different, Excel may guess wrong. A column might be mostly numbers, but later contain text, causing errors.

2.  Based on the full dataset 

Excel scans all rows before deciding the data types.

What this means:

• More accurate interpretation of your data
• Reduces mistakes caused by unusual or late-occurring values

Problem:

• Slower, especially with large files
• Uses more processing time

3.  Don’t detect (treat everything as text) 

Excel does not try to understand your data at all.

What this means:

• Everything is imported as plain text
• No automatic guessing or formatting happens

Why this is useful:

• Prevents Excel from making wrong assumptions
• Very safe option for messy or inconsistent data
• You can decide the correct data types later manually in Power Query

You can change these settings to whichever works best for you.

Step 4: Preview your data

• Excel will show you a preview of how your data will look
• Check if columns are split correctly and values look right
• Click Transform Data to open Power Query Editor if you need to make some adjustments to your data. If not, choose Load to import directly

2. Importing Data from the Web

Power Query has the ability to pull data directly from web pages, APIs, and online files. But the thing I find most interesting about importing from the web is that if the source updates regularly, you can just hit refresh in your Excel workbook and your cleaned dataset updates automatically. No manual rework needed.

Step 1: Connect to a website

• Go to the Data ribbon
•  Get Data 
•  From other sources select From Web 

Step 2: Enter the URL 

• In most cases, you just paste the full web link (URL) 

• In Advanced mode, you can build and control how Power Query connects to a web source.

For instance, instead of pasting a single URL, you might split it into parts by clickingAdd Part to add parts of the URL. You can also set a command timeout and define HTTP headers.

For Example:

Step 3: Select authentication type

Depending on the source, you may need to authenticate:

• Anonymous: For public websites (No need to sign in)
• Basic: Username and password
• Windows: Sites using your Windows credentials
• Web API: API key
• Organizational account: Work or school login

Step 4: Navigate and select tables

• The Navigator shows all detected tables on the page

• You can use the Web View tab to see the full HTML and highlight tables visually

• Select Add table using examples for non-standard table structures

Step 5: Load or transform the data

• Click Load if everything looks good
• Or choose Transform Data to clean and shape it before using it

NB: The Web connector also supports importing these file types directly:

CSV

JSON

XML

Excel (.xlsx) 

Access (.accdb) 

HTML

PDF

Just point to the file URL, and Power Query auto-detects the format.

3. Importing Data from Databases

Power Query can connect directly to databases, allowing you to pull large amounts of structured data into Microsoft Excel without manually exporting files. Instead of copying data or working with static reports, you connect Excel directly to the database and import only what you need.

How it works

Step 1: Choose your data source

• Go to the Data ribbon
• Get Data 
• From the Database, select the database you want

You can connect to different types of databases, such as:

This is just a few out of over 100 different database sources. 

Step 2: Enter connection details

You may need to provide:

• Server name or database address
• Database name
• Login details (username and password)

Step 3: Select the data you want

After connecting, Power Query shows available tables in the database.
You can:

• Select one or more tables
• Preview the data before importing

Step 4: Load or transform the data

• Click Load to bring it directly into Excel
• Or click Transform Data to clean and shape it first in Power Query

Why is this useful?

Similar to importing data from the web, connecting directly to a database helps you avoid outdated data and eliminates the need to repeatedly import files. With a simple refresh in Microsoft Excel, your data is always kept up to date through Power Query. 

Conclusion

Power Query transforms Excel from a static spreadsheet into a dynamic data hub. By mastering these three important methods, CSV, Web, and Databases, you can connect to virtually any data source, apply consistent transformations, and keep your data fresh with scheduled refreshes.