This is an extension of the previous post.

Here’s the task:

If the data is coming from a CSV file, the solution is super easy.

You may download the sample CSV file to follow along.

Simply get the data using Power Query as below:

1. Go to Data Tab
2. From File
3. From Text/CSV
4. Select the file of the data; then click Import (Not shown in the screenshot below 😅)

Please pay attention to the preview of the data. We can see that the Product Code is of various digits. As mentioned in the previous post – Convert numbers of different digits into text of fixed digits, Excel is too smart to convert “Number stored as Text” to number automatically.

No worry! It’s just a preview before Loading the data. Let’s select “Transform Data” (or “Edit”) to go into the Power Query Editor where magic happens.

In the Power Query Editor, we see that the first column has converted into number by the auto-generated step “Changed Type”.

To convert it back to Text, simply

1. Click the “123” icon on the column header of “Product Code”
2. Select “Text“
3. Make sure you select “Replace current” (NOT Add new step).

Here we go!

The original “Text” from the CSV file is preserved. As simple as this! 🙌

Note: If we selected “Add new step” instead in the previous step, Power Query would add a new step based on the converted numbers (without the leading zeros), resulting in one more step and undesired result. 🤦‍♂️

What if the leading zeros have already been trimmed in the data file?

Solving the problem with a simple function

Like what we did with Excel function “TEXT”, we can use the Power Query function “Number.ToText” to do so.

First, we need to add a custom column

1. Go to Add Column tab of Power Query Editor
2. Custom Column

In the Custom Column Dialogue box,

1. Name the custom column that we are about to create
2. Input the following formula: Number.ToText([Product Code], “0000000000000”) //(13 zeros)
3. OK

Here we go!

Did you notice the similarity of the two functions?

Excel function:

=TEXT(A2,"0000000000000")

Power Query function:

=Number.ToText([Product Code], "0000000000000")

They both takes two arguments: First, the value to convert; second, the format string required.

Not too complicated, is it?

Note: As the function name (Number.ToText) says it all, it applies to number. Please ensure the column has been defined as number before adding the custom column.

Well… if you are not comfortable to add a custom column with Power Query function / formula, there is another way to achieve the same via UI only.

Solving the problem with UI only

To make sure all Product Code carry thirteen digits, we need to perform two steps:

1. Add back “enough” leading zeros
2. Extract the last thirteen digits

But how do we know how many leading zeros are enough?

We may first check the least number of digits we have in the column. Select the column and then

1. Go to Transform tab
2. Extract
3. Lengths

In our example, the least number of digits in the column is eleven. This means two leading zeros is good enough.

Now, let’s delete the last step of “Calculated Text Length”.

We may add the leading zeros now. Select the column “Product Code”,

1. Go to Transform tab
2. Format
3. Add Prefix
4. Input “00” (double quotes are not required)
5. OK

As a result, we will have a column of Product Code with at least thirteen digits (with leading zeros).

Now we are ready to restore the original 13-digit “Number stored as text” by extracting the last thirteen characters.

Select the column “Product Code”, then

1. Go to Transform tab
2. Extract
3. Last Characters
4. Input 13
5. OK

Mission accomplished with UI only.

Limitation

However, you may have noticed a limitation of this approach. We had hard-coded the number of “leading zeros”. What if we have a new data file that contains Product Code of nine digits only? Obviously, we won’t be able to get a 13-digit text by adding two leading zeros to a 9-digit number.

One (not too perfect) way to avoid the pitfall is to add X leading zeros, where X is the number of digits we want in the result. At the end, we will get a fixed length of X letters by extracting the last X characters. Make sense?

Although it can do the task, it’s not “elegant”. Therefore, the Number.ToText approach wins. 😁

What do you think? How do you solve this problem? Please share with us by leaving comments below.