Power Query: How to import numbers as text

2
votes

I have a csv file to import to Power Query (250,000 records). One field contains our fee codes which are usually entered as numbers but sometimes contain characters (e.g. 17, 17A, 67, 67A, etc). When I import the file to Power Query, the field is treated as a numeric column and all the data with letters is not imported. I can convert the field to text AFTER the import - but by then it is too late and I have lost all the non numeric data. How can I tell Power Query to bring in this field as TEXT not as a number?

Is there an easy way to change the way the data is imported without having to change the data file or manually create a schema file? P.S. I am new to Power Query so this may be something simple that I have overlooked - but I really have looked!

1
textimportnumberspowerquery

1 Answers

1
votes

Power Query adds an automatic type conversion step after certain data sources like CSV. If you look at the Applied Steps list you'll see that the last step is one that changes the type. You can delete that step to undo the type change.

You can also edit the step by making the formula bar visible (which can be enabled by going to the View ribbon and checking the Formula Bar checkbox) and then editing the column type. For example, if your formula was:

= Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type datetime}, {"ID", Int64.Type}})

you can change ID to be a text type by changing Int64.Type to type text.