How to How to Use Get & Transform Data in Excel in Excel - Practical Guide

Learn to use Get & Transform (Power Query) to import, clean, and reshape data from multiple sources without manual entry. This skill eliminates repetitive data preparation tasks, enabling you to focus on analysis while maintaining data integrity and automating updates.

Why This Matters

Get & Transform saves hours on data preparation and enables dynamic data pipelines that update automatically when sources change. It's essential for professionals handling large datasets from diverse sources.

Prerequisites

•Basic Excel knowledge and familiarity with worksheets
•Access to Excel 2016 or later (Power Query included)
•Data source accessible (CSV, database, web, or Excel file)

Step-by-Step Instructions

Access Get & Transform Feature

Navigate to Data tab > Get Data (or Get & Transform Data in older versions) > choose your source type (From File, From Database, From Web, etc.).

Select and Load Data Source

Browse and select your data file or enter connection details. Click Load or Load To to proceed to the Power Query editor or import directly.

Transform Data in Power Query Editor

In the Query Editor, use Home tab tools: Remove Columns, Remove Rows, Change Data Type, Split Column, and Filter to clean and reshape data as needed.

Apply Transformations and Create Queries

Review applied steps in the Query Settings panel on the right. Rename your query in the Properties section for easy identification.

Load Cleaned Data to Worksheet

Click Home > Close & Load > Load To to import into a new table or existing location. Set refresh schedule via Data > Queries & Connections > Refresh.

Alternative Methods

Direct Load Without Transformation

Use Data > Get Data > Load directly to skip the Power Query editor if no cleaning is needed. Ideal for pre-cleaned data sources.

Use Linked Tables from Excel Sources

For Excel files, use Data > New Query > From Other Sources > From Table/Range for simpler inline transformations without full Power Query.

Import via CSV Wizard Legacy Method

Use Data > Get External Data (older Excel) for basic imports, though Get & Transform is the modern recommended approach.

Tips & Tricks

✓Always remove unnecessary columns early in the transformation process to reduce file size and improve performance.
✓Use Merge Queries to combine data from multiple sources without VLOOKUP formulas.
✓Rename steps in Query Settings for clarity; descriptive names help when revisiting transformations later.
✓Preview data at each transformation step to catch errors before final load.
✓Set data types explicitly after loading to prevent Excel from auto-detecting incorrectly (dates, text codes).

Pro Tips

★Use Custom Columns with formulas to create calculated fields within Power Query instead of in Excel afterward.
★Leverage Unpivot for transforming wide data (many columns) into long format (many rows) for better analysis.
★Enable automatic refresh schedules (Data > Refresh All) to keep linked data current without manual intervention.
★Use Append Queries to combine data from multiple similar tables in one operation instead of copy-paste.
★Combine Get & Transform with PivotTables for powerful self-updating dashboards that reflect source changes.

Troubleshooting

Query fails to load with 'connection error'

Verify the data source path is correct and accessible. For web sources, check internet connection and website availability. For databases, confirm credentials and server access.

Data types appear wrong after load

Return to Power Query editor, select affected column, go to Home > Data Type, and manually set the correct type (Text, Number, Date, etc.).

Refresh takes too long or times out

Optimize by removing unnecessary columns, reducing row counts with filters, or splitting large queries into smaller ones that load in parallel.

Special characters or encoding issues in imported data

In Get Data dialog, specify encoding before import (e.g., UTF-8). For CSV files, try different delimiters or encodings in the import preview.

Query editor won't open or freezes

Close and reopen Excel, clear query cache (Data > Queries & Connections > right-click > Delete), or restart Power Query add-in from File > Options > Add-ins.

Related Excel Formulas

VLOOKUP INDEX MATCH FILTER SORT UNIQUE

Frequently Asked Questions

Is Get & Transform the same as Power Query?

Yes, Get & Transform is Microsoft's name for the Power Query feature built into Excel (Excel 2016+). Power Query is the underlying technology.

Can I use Get & Transform with databases like SQL Server?

Absolutely. Use Data > Get Data > From Database > SQL Server (or other databases) to connect directly, write queries, and import filtered results.

How do I update the data in my worksheet automatically?

After loading, right-click your query in Queries & Connections or use Data > Refresh All. Set automatic refresh in Data > Queries & Connections > Refresh settings.

Can I combine data from multiple sources into one table?

Yes. Use Merge Queries for joins (like VLOOKUP) or Append Queries to stack similar tables vertically. Both operations happen within Power Query.

Does Get & Transform work in Excel Online or only desktop?

Get & Transform is primarily a desktop Excel feature. Excel Online has limited support; for full functionality, use Excel 365 desktop application.

What's the difference between Load and Load To?

Load imports data to a new worksheet table automatically. Load To gives you options to load to a specific location, create a connection only, or add to Data Model.