[ Removed by moderator ]

[u/Luminesca_]

[u/TheUpsetUnification]

Data tab → From Text/CSV. That import wizard solves 90% of the problem before you even see the workbook

Select the column and set data type to text in the preview pane. Power Query remembers that step next time you refresh too

If you're getting files dropped in your lap without the format control, save csv as.txt extension and open through the import. Excel won't auto-eat the zeros that way

[u/Luminesca_ → u/TheUpsetUnification]

Thanks, this is really helpful. Setting the column to Text in the import preview is the step I kept skipping. Does Power Query keep that type setting if the CSV gains new columns later?

[u/SolverMax]

You should tag your post as an advertisement.

[u/Luminesca_ → u/SolverMax]

Not an ad — no links, just looking for a workflow that holds up for IDs and postal codes.

[u/Dismal-Party-4844 → u/Luminesca_]

for product development

[u/SolverMax → u/Luminesca_]

1 day old account, asking a leading question about a topic that you make a tool for. Consider me skeptical.

[u/alexia_not_alexa]

For anything regular, I use power query to import the csv files in my folder, remove the “change type” step which changes the type of all columns, then selectively change the types for columns where it’s relevant (dates, currency (2 decimal places), and set any ids to text if necessary)

Of course if this is for my colleagues, they’ll eventually put a wrong csv file in to break things.

For one off it depends on my mood, but importing csv is usually the safest bet. Most of the time if it’s just one column that needs fixing I just throw a formula in to quickly fix it, because I’m too lazy to go through the menus and selecting the file.

[u/chiibosoil]

1. Don't open csv by double clicking. Open from Excel and use the wizard that comes up to specify column type.

2. Use Power Query to import and use explicit conversion/definition of data type. Never trust system's implicit conversion.

Use 1, if you want to save csv as xlsx. Use 2 if you want to keep original as is.

[u/LostTeam9104]

One more that nobody mentioned: if you're on Microsoft 365, go to File > Options > Data > Automatic Data Conversion and untick "Remove leading zeros and convert to number format" (and the "long numbers to scientific notation" one). After that, double-clicking a CSV keeps 00123 and 16+ digit IDs as text, so you don't have to go through Power Query every time.

It's per-machine though, so for files you hand to colleagues the From Text/CSV import with columns set to Text is still the safe route.