This can be a typical case for Self-Service BI with Excel data.
Just a few days ago, a client asked me the next query:
I actually have an Excel sheet with numbers and text in a single column. I would like to import this sheet into Power BI and perform evaluation on the numbers in that column.
How can I separate the numbers from the text in that column?
Do not forget that I would like the text in that column as well.
I had never been in this example, so I first began using the technique I knew.
I created a Dummy Excel with the identical problem, which looks like this:
To create a PoC, I first loaded this data into an SQL Server database to see how one can solve it there.
Solving the issue by utilizing SQL
T-SQL has two functions which are helpful in such scenarios:
- TRY_CONVERT()
- This one tries to convert a worth to a goal data type. If it fails, it returns NULL.
- ISNUMERIC()
- Checks if a worth is a numeric value. If yes, it returns 1. Otherwise, 0.
Based on this data, I wrote a question to separate the values into two columns. One with the numbers and one with the text:
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 table:
Should you look fastidiously, you see that row 17 is recognized as a text.
It’s because the number incorporates a blank.
I’ll come back to this in a while.
Switching to Power Query – Trying IsNaN()
Now, I loaded the Excel into Power Query.
I defined the column as Text and commenced working on this challenge.
The primary attempt uses the Number.IsNaN() function.
This function returns true if the worth is NaN. “NaN” is a placeholder for not applicable, for instance, due to a division by 0.
I attempted this to find out whether a text is corresponding to NaN.
That is the M-Code for the calculated column:
if Number.IsNaN([Value]) = true
then [Value]
else nullThe result surprised me:
Strangely, the result’s that it cannot convert a number to a number.
I suppose this happens since the column’s data type is text.
Then, I attempted converting the column to a number and applying the IsNaN() function to the result:
if Number.IsNaN(Number.From([Value])) = false
then Number.From([Value])
else nullNow, the numbers are converted to numbers, however the text values lead to an error:
Now the logic works for numbers.
However the conversion fails for the rows containing text. This ends in rows with errors.
Trying Value.Is() in Power Query
Let’s try one other function: Value.Is()
This function checks whether a worth is compatible with a knowledge type.
This needs to be corresponding to the ISNUMERIC() function shown above:
if Value.Is([Value], Number.Type) = true
then Number.From([Value])
else nullUnfortunately, this function did not return the expected result as well:
After I tried the identical approach as above, by converting the worth to a number first, I got the identical result as before:
Subsequently, I think that the function Value.Is() expects a number data type, but this is mindless to me.
At this point, I didn’t have time for deeper research, as I used to be running short on time.
It was time to change the approach.
Switching concept
Now I explored how one can catch errors in Power Query.
My idea was: What if I could catch the conversion error and use this information?
I discovered this page with useful information: Errors – PowerQuery M | Microsoft Learn
From this, I deduced this expression:
try Number.From([Value]))After adding a calculated column with this expression, I got this result:
I used to be optimistic, as I didn’t get an error.
Next, was to expand the Records:
I didn’t need the Error columns—only the Value column.
That is the result after the expansion:
Notice that I renamed the columns directly within the ExpandRecordColumn() function.
Otherwise, I’d have gotten a column named [Value.1].
This result was the primary where I didn’t get any errors.
Now, I added a calculated column to examine if the brand new column is empty. If yes, then the unique Value column contained a text:
if [Numeric Value] = null then [Value] else nullHere, the result:
After setting the right data types and removing the unique Value column, I got this table:
Handle the number with blanks
But we still have row 17, which contained a number with a blank.
How did I handle this?
Essentially the most straightforward approach was to remove any Blank from the column Value:
But I had so as to add this step before starting the steps for separating the 2 value types:
After adding this step, row 17 is recognized as a number and stored accurately.
Here is the information after loading it into Power BI:
But this only worked if the text values were single words. It didn’t work when sentences or multiple words were stored there.
Conclusion
This was an enchanting tour into how Power Query, or the M-language, works with data types.
I’m still unsure concerning the causes of the errors.
But I learned how one can handle errors, or how one can use the try call and handle the output.
This was very helpful.
Anyway, as you see with the unique value in row 17, data quality is paramount.
I actually have one other client where users from different countries are working on the identical Excel file with their very own number formats.
This can be a nightmare because Excel is extremely tolerant of information types. It accepts every part, even when the column is formatted as a number.
In that situation, I have to force users to make use of Excel’s formatting options to make sure that numbers are consistently recognized as such.
Without this, I actually have no likelihood to import this data into Power BI without numerous effort to wash up the numbers.
And stay assured that users all the time discover a technique to mess up with numbers in Excel.
References
The Data is created with random numbers and words.
Here is the reference for the M-Language: Power Query M formula language reference – PowerQuery M | Microsoft Learn
