Use Power Query to Import CSV Files Without Losing Leading Zeros

Table of Contents

😫 You export a CSV from your internal system, but every time you open it in Excel, the leading zeros disappear (e.g., 00123 becomes 123), breaking product codes and customer IDs?

🗂️ Category: Advanced Excel Functions

⭐ Difficulty: ★★★★☆

📝 Content

When opening CSV files directly in Excel, fields like product codes, ZIP codes, and employee IDs are often auto‑converted to numbers—removing important leading zeros.

This causes issues with lookups, reporting, and data matching.

Power Query gives you full control over how each column is interpreted. By explicitly setting a column to Text, you preserve formatting exactly as in the source file—every time, automatically.

This is a cleaner, more reliable method than manual formatting, especially for recurring imports.

✅ Benefits

• Prevents Excel from auto‑converting IDs into numbers
• Ensures consistent data accuracy across imports
• Ideal for automation, recurring reports, and system integration
• Reduces manual cleanup and improves data reliability

🛠️ Procedure

1. In Excel, go to Data → Get Data → From File → From Text/CSV.

2. When the preview window appears, click Transform Data (don’t load directly).

3. In Power Query:

• Select the column with leading zeros
• Change Data Type → Text
• Click Close & Load
• Your data now loads correctly every time, with leading zeros intact.

📎 Notes

If the same CSV is imported regularly, Power Query will remember the formatting rules.

Combine with column trimming, splitting, or replacing errors for full ETL automation.

Useful for ERP, SAP, CRM, PLM, or barcode data exports.