Forum Discussion
Cannot Convert Text to Decimal
Hi everyone!
I think I tried posting this question but I'm not sure if it went through because I had to create an account.... so apologies if this is a re-post! I am new to the Power BI Community.
I'm looking for some help regarding data that is being pulled from Microsoft Planner and the type is being pulled as text. I've attached a screenshot below of the data I am trying to convert to a decimal. For example, it shows the completed checklist items out of total checklist items, but shows 3/5 as text. I want to convert it to show .6 or 60% instead, but when I do this, I get a column full of errors. Any help would be very much appreciated.
Thank you!Screenshot of text
Hi Anonymous ,
Power BI can't recognize the "/" as "÷".
You can use Split feature in Query Editor then add a new column =column1/column2
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hi Anonymous ,
Power BI can't recognize the "/" as "÷".
You can use Split feature in Query Editor then add a new column =column1/column2
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi AlexandraC7,
The easiest way to get percentages is to perform these actions using additional columns.
1. Separate the denominator and denominator, and then add the column that will perform the (Custom) action.
2. Delete unnecessary columns.#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter("/", QuoteStyle.None), {"Numerator", "Denominator"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Numerator", Int64.Type}, {"Denominator", Int64.Type}}), #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Custom", each if [Denominator] = null then [Numerator] else [Numerator]/[Denominator]), #"Changed Type2" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Custom", Percentage.Type}})
_______________
If I helped, please accept the solution and give kudos! 😀Hi Anonymous
it looks like your "null" is not exactly null.
try to replace value to to '' (blank) before convert the data type
New column =
var _pos = search("/",[column],,0)
var _len =len([column])
return
if(isblank([column]),0,divide(left([column],_pos-1),right([column],_len-_pos)))
