Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hello everyone,
I have a column that was created with DAX (hence cannot use power query) as a Text that needs to be converted to Number.
However, sometimes on this column there are non-numerical, non-roman characters as the source is taken from multiple languages around the world.
When using CONVERT function, if only in one of the rows there is a special character, all rows are changed to #Error.
I want to know if there is a function that forces the convertion (omitting or deleting the special character) or at least that changes to #Error only the row with the problem, and not the entire column.
Many thanks,
Hello,
The solution provided Did not work for the intended purpose at all.
When using it I got an error: "Expressions that yield variant data-type cannot be used to define calculated columns".
An Example of my problem, and how I would like the solution (using 0.04 as error code)
14.45 --> 14.45
14# --> 0.04
103 --> 103
$18 --> 0.04
when trying to use 0.04 instead of "Not number", all rows became 0.04
Any other idea with this?
Hi @NPbon ,
Plese try to identify the column firstly, then convert the data type, please try to create a column like below dax formula:
Column =
VAR cur_val = [Column Name]
VAR _a =
SWITCH (
TRUE (),
ISNUMBER ( cur_val ), CONVERT ( cur_val, INTEGER ),
ISNONTEXT ( cur_val ), CONVERT ( cur_val, INTEGER ),
"Not number"
)
RETURN
_a
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
8 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
9 |