I can not convert text to number

[Anonymous]

Hello to all

I have a calculate column that gets it's values from another table that returns text values. The vaues are something like:

10

""

10d

10 to 40

N/A

Blank

I tried to use Format, and direct column conversion. Did not work.

I tried to make the empty columns to 0 and the rest to the value and it worked, but when formating it gave the multy type variant error.

What can I do?

Thanks

[KHorseman · Accepted solution]

When you say you made the empties 0, do you mean you replaced "", N/A, and Blank with 0? Or do you also have completely blank rows that you've filled with 0, leaving the values of "", N/A, and Blank intact? If you've left those three values, those are all text values that you must replace with numbers before you can convert. But even if you've done that you still have 3 other values listed and only 1 of those is a number.

10

10d

10 to 40

10 is a number. "10d" and "10 to 40" are not. I assume when you list "10 to 40" as a single item you mean there are rows that literally contain the text "10 to 40" and you don't mean that all values between 10 and 40 are present somewhere in the column, since you listed "10" separately at the start of your list of values. So what number should "10 to 40" be treated as? 10? 40? 31? 29? 17? A numerical value can only be a single numerical value, not a range.

Then there's 10d. 10d isn't a number. What's 10d? Should that be 10?

[Anonymous]

Yes, I try to replace all the "", N/A, and Blank with 0.

But the rest I see that is not possible. 10d would be 10, and we should not use the "x to y" format...

Need to validate the data before arriving to power BI. 😞

[v-ljerr-msft]

Hi @Anonymous,

---

Need to validate the data before arriving to power BI. 😞

---

Yes, it's always a good practice to use consistent format for the values in each column of your queries.

Regards