Separate Numbers and Text in One Column Using Power Query

It is a typical case for Self-Service BI with Excel information.

A number of days in the past, a consumer 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?

Keep in mind that I want the textual content in that column as properly.

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

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

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

Fixing the issue through the use of SQL

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

• TRY_CONVERT()
  • This one tries to transform a worth to a goal information 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.

Based mostly 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:

Should you look fastidiously, 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 afterward.

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 not convert a quantity to a quantity.

I suppose this occurs as a result of the column’s information 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 strive one other perform: Value.Is()

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

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

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

Sadly, this perform did not return the anticipated outcome as properly:

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

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

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

It was time to change the method.

Switching idea

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

My concept was: What if I may catch the conversion error and use this info?

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

From this, I deduced this expression:

strive Quantity.From([Value]))

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

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

Subsequent, was to develop the Information:

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

That is the outcome after the enlargement:

Discover that I renamed the columns straight within the ExpandRecordColumn() perform.
In any other case, I might 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 test 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 proper information varieties and eradicating the unique Worth column, I acquired 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 simple method 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 appropriately.

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

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

Conclusion

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

I’m nonetheless uncertain concerning the causes of the errors.

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

This was very useful.

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

I’ve one other consumer the place customers from totally different international locations are engaged on the identical Excel file with their very own quantity codecs.

It is a nightmare as a result of Excel is very tolerant of knowledge varieties. It accepts all the pieces, 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 information into Energy BI with out a whole lot of effort to wash up the numbers.

And keep assured that customers all the time discover a solution 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