Separate Numbers and Text in One Column Using Power Query

This can be a typical case for Self-Service BI with Excel knowledge.

A couple of days in the past, a shopper requested me the next query:

I’ve an Excel sheet with numbers and textual content in a single column. I wish to import this sheet into Energy BI and carry out evaluation on the numbers in that column.

How can I separate the numbers from the textual content in that column?

Do not forget that I would like the textual content in that column as effectively.

I had by no means been on this scenario, so I first began utilizing the approach I knew.

I created a Dummy Excel with the identical downside, which seems like this:

To create a PoC, I first loaded this knowledge into an SQL Server database to see how you can clear up it there.

Fixing the issue by utilizing SQL

T-SQL has two features which are useful in such situations:

• TRY_CONVERT()
  • This one tries to transform a worth to a goal knowledge sort. If it fails, it returns NULL.
• ISNUMERIC()
  • Checks if a worth is a numeric worth. If sure, it returns 1. In any other case, 0.

Primarily based on this data, I wrote a question to separate the values into two columns. One with the numbers and one with the textual content:

SELECT [Values]

            ,TRY_CONVERT(decimal(18, 5), [Values])         AS    [Number]

            ,IIF(ISNUMERIC([Values]) = 0, [Values], NULL) AS    [Text]

  FROM [dbo].[MixedValues];

The result’s the next desk:

When you look rigorously, you see that row 17 is acknowledged as a textual content.

It’s because the quantity incorporates a clean.

I’ll come again to this in a while.

Switching to Energy Question – Making an attempt IsNaN()

Now, I loaded the Excel into Energy Question.

I outlined the column as Textual content and began engaged on this problem.

The primary try makes use of the Number.IsNaN() perform.

This perform returns true if the worth is NaN.  “NaN” is a placeholder for not relevant, for instance, due to a division by 0.

I attempted this to find out whether or not a textual content is equal to NaN.

That is the M-Code for the calculated column:

if Quantity.IsNaN([Value]) = true
then [Value]
else null

The outcome stunned me:

Unusually, the result’s that it can’t convert a quantity to a quantity.

I suppose this occurs as a result of the column’s knowledge sort is textual content.

Then, I attempted changing the column to a quantity and making use of the IsNaN() perform to the outcome:

if Quantity.IsNaN(Quantity.From([Value])) = false
then Quantity.From([Value])
else null

Now, the numbers are transformed to numbers, however the textual content values lead to an error:

Now the logic works for numbers.

However the conversion fails for the rows containing textual content. This ends in rows with errors.

Making an attempt Worth.Is() in Energy Question

Let’s attempt one other perform: Value.Is()

This perform checks whether or not a worth is appropriate with a knowledge sort.

This ought to be equal to the ISNUMERIC() perform proven above:

if Worth.Is([Value], Quantity.Sort) = true
then Quantity.From([Value])
else null

Sadly, this perform didn’t return the anticipated outcome as effectively:

Once I tried the identical strategy as above, by changing the worth to a quantity first, I obtained the identical outcome as earlier than:

Subsequently, I think that the perform Worth.Is() expects a quantity knowledge sort, however this is senseless to me.

At this level, I didn’t have time for deeper analysis, as I used to be operating brief on time.

It was time to modify the strategy.

Switching idea

Now I explored how you can catch errors in Energy Question.

My thought was: What if I might catch the conversion error and use this data?

I discovered this web page with helpful data: Errors – PowerQuery M | Microsoft Learn

From this, I deduced this expression:

attempt Quantity.From([Value]))

After including a calculated column with this expression, I obtained this outcome:

I used to be optimistic, as I didn’t get an error.

Subsequent, was to increase the Data:

I didn’t want the Error columns—solely the Worth column.

That is the outcome after the enlargement:

Discover that I renamed the columns instantly within the ExpandRecordColumn() perform.
In any other case, I’d have gotten a column named [Value.1].

This outcome was the primary the place I didn’t get any errors.

Now, I added a calculated column to verify if the brand new column is empty. If sure, then the unique Worth column contained a textual content:

if [Numeric Value] = null then [Value] else null

Right here, the outcome:

After setting the right knowledge varieties and eradicating the unique Worth column, I obtained this desk:

Deal with the quantity with blanks

However we nonetheless have row 17, which contained a quantity with a clean.

How did I deal with this?

Probably the most easy strategy was to take away any Clean from the column Worth:

However I had so as to add this step earlier than beginning the steps for separating the 2 worth varieties:

After including this step, row 17 is acknowledged as a quantity and saved accurately.

Right here is the info after loading it into Energy BI:

However this solely labored if the textual content values have been single phrases. It didn’t work when sentences or a number of phrases have been saved there.

Conclusion

This was an interesting tour into how Energy Question, or the M-language, works with knowledge varieties.

I’m nonetheless not sure in regards to the causes of the errors.

However I discovered how you can deal with errors, or how you can use the attempt name and deal with the output.

This was very useful.

Anyway, as you see with the unique worth in row 17, knowledge high quality is paramount.

I’ve one other shopper the place customers from completely different nations are engaged on the identical Excel file with their very own quantity codecs.

This can be a nightmare as a result of Excel is very tolerant of information varieties. It accepts every thing, even when the column is formatted as a quantity.

In that scenario, I need to drive customers to make use of Excel’s formatting choices to make sure that numbers are persistently acknowledged as such.

With out this, I’ve no probability to import this knowledge into Energy BI with out lots of effort to wash up the numbers.

And keep assured that customers at all times discover a approach to mess up with numbers in Excel.

References

The Knowledge is created with random numbers and phrases.

Right here is the reference for the M-Language: Power Query M formula language reference – PowerQuery M | Microsoft Learn