Data Import and Power Query

🌍 External Data & Power Query: Bringing the World Into Excel

Imagine you’re a chef. You don’t grow all your ingredients—you get them from farms, markets, and suppliers. Excel works the same way! Your data doesn’t have to live inside Excel. You can bring it in from text files, websites, and more. That’s what External Data is all about!

🎯 The Big Picture

Think of Excel as your kitchen and external data as ingredients from outside:

• Text files = Fresh vegetables from a farm
• Web data = Exotic spices from faraway places
• Power Query = Your magical sous-chef who cleans, chops, and prepares everything perfectly

Let’s learn how to bring the world’s data into your spreadsheet!

📄 Importing from Text Files

What Are Text Files?

Text files are the simplest way to store data. They’re like notes written on paper—just plain text, no fancy formatting.

Two common types:

How to Import: Step by Step

1. Open Excel → Go to Data tab
2. Click Get Data → From File → From Text/CSV
3. Pick your file → Excel shows a preview
4. Check the preview → Make sure columns look right
5. Click Load → Done! 🎉

Real Example

Imagine you have a file called fruits.csv:

Name,Color,Quantity
Apple,Red,10
Banana,Yellow,15
Orange,Orange,8

Excel reads each comma as “start a new column.” So you get a nice table with 3 columns!

💡 Pro Tip

If your data looks squished into one column, you might have the wrong delimiter (separator). Check if it’s comma, tab, or semicolon!

🌐 Importing from the Web

The Internet is a Giant Database!

Websites have tables everywhere—sports scores, stock prices, weather data. Excel can grab these tables automatically!

How to Import: Step by Step

1. Find a webpage with a table you want
2. Copy the URL (web address)
3. In Excel: Data → Get Data → From Other Sources → From Web
4. Paste the URL → Click OK
5. Excel shows all tables on that page
6. Pick your table → Click Load

Real Example

Want today’s weather for 10 cities? Find a weather website with a table, paste the URL, and Excel pulls it right in!

graph TD
    A["🌐 Website with Table"] --> B["📋 Copy URL"]
    B --> C["📊 Excel: From Web"]
    C --> D["🎯 Select Table"]
    D --> E["✅ Data in Excel!"]

⚠️ Important Note

Not all websites allow data import. If it doesn’t work, the website might be blocking it—that’s okay, try another source!

⚡ Power Query Introduction

Meet Your Magical Helper!

Power Query is like having a super-smart assistant who:

• 🧹 Cleans messy data automatically
• 🔄 Remembers every step you take
• 🔁 Repeats the same work whenever you want

Why Power Query is Amazing

Where to Find It

Power Query lives in the Data tab. Look for these buttons:

• Get Data (starting point for everything)
• Queries & Connections (see your saved work)

Simple Analogy

Think of Power Query as a recipe book for data:

1. You write down each step (remove this column, fix these names)
2. Save the recipe
3. Next time, just press “cook” and it does everything automatically!

🔧 Get and Transform Data

The Starting Point

“Get and Transform” is just another name for Power Query. It means:

1. GET = Bring data into Excel
2. TRANSFORM = Clean and shape it

The Get Data Menu

Click Data → Get Data and see all your options:

graph TD
    A["Get Data"] --> B["From File"]
    A --> C["From Database"]
    A --> D["From Web"]
    A --> E["From Other Sources"]
    B --> F["Excel, CSV, Text, etc."]
    D --> G["Any webpage URL"]

Real Example: Getting Sales Data

Let’s say you get a new sales file every Monday:

1. First time: Use Get Data to import the file
2. Power Query remembers the file location and all your changes
3. Every Monday: Just click Refresh and new data appears, perfectly cleaned!

🎛️ Query Editor Basics

Your Data Workshop

When you click Transform Data, you enter the Query Editor. This is where the magic happens!

The Interface (What You See)

The Most Important Part: Applied Steps

Every change you make appears as a “step” on the right side. Like breadcrumbs showing your path!

graph TD
    A["Step 1: Source"] --> B["Step 2: Changed Type"]
    B --> C["Step 3: Removed Columns"]
    C --> D["Step 4: Filtered Rows"]
    D --> E["Final Result!"]

💡 Super Power

Click any step to go back in time! Made a mistake at Step 3? Click Step 2, delete Step 3, and try again. It’s like having an undo button for EVERYTHING.

🔄 Data Transformations

Making Data Perfect

Transformations are changes you make to clean and shape your data. Here are the most common ones:

1. Remove Columns 🗑️

When: You have 20 columns but only need 5

How:

• Right-click the column header
• Choose “Remove”

2. Filter Rows 🔍

When: You only want certain rows (like sales > $100)

How:

• Click the dropdown arrow in the column header
• Uncheck what you don’t want

3. Change Data Type 📝

When: Numbers show as text (you can’t add them!)

How:

• Click the icon in the column header (ABC or 123)
• Choose the correct type

4. Replace Values 🔁

When: “N/A” should be empty, or “NY” should be “New York”

How:

• Transform → Replace Values
• Type what to find and what to replace with

5. Split Columns ✂️

When: “John Smith” should be two columns: “John” and “Smith”

How:

• Right-click the column
• Split Column → By Delimiter (like space)

Quick Reference Table

📥 Loading Query Results

Bringing Data Back to Excel

After transforming your data in Query Editor, you need to load it back to Excel.

Loading Options

When you click Close & Load, you get choices:

How to Load

1. Finish your transformations
2. Click Close & Load (or the dropdown arrow for options)
3. Choose where to put the data
4. Done! 🎉

graph TD
    A["Query Editor"] --> B["Close & Load"]
    B --> C{Where to Load?}
    C --> D["New Worksheet"]
    C --> E["Existing Worksheet"]
    C --> F["Only Create Connection"]

💡 Smart Tip

Choose “Only Create Connection” when:

• The data is huge and you don’t need to see it all
• You’re combining multiple queries later

🔃 Refresh Connections

Keeping Data Fresh

Your data changes over time. Sales numbers update. Websites show new information. Refresh brings in the latest data!

How to Refresh

Single Query:

• Right-click the table
• Click Refresh

All Queries:

• Data tab → Refresh All

Automatic Refresh:

• Right-click query → Properties
• Check “Refresh every X minutes”

What Happens When You Refresh

graph TD
    A["Click Refresh"] --> B["Excel goes to source"]
    B --> C["Gets new data"]
    C --> D["Applies all your steps"]
    D --> E["Updates your table"]

Real Example

Every Monday, your boss sends a new sales file with the same name.

Old way: Delete old data, import new file, fix everything again (30 minutes)

Power Query way: Click Refresh (3 seconds!)

⚠️ Important

If the source file is moved or renamed, refresh will fail. Keep your source files in the same place!

🎯 Quick Summary

🚀 You Did It!

You now know how to:

• ✅ Import data from text files
• ✅ Pull tables from websites
• ✅ Use Power Query to clean data
• ✅ Transform messy data into clean data
• ✅ Load results back to Excel
• ✅ Keep everything fresh with Refresh

Remember: Power Query is like teaching Excel a recipe. Do it once, and Excel cooks it perfectly every time!

Now go bring the world’s data into your spreadsheets! 🌍📊