Forum Discussion
Conditionally transform percentage to integer
I have some mixed data entered in a table column (some are percentages, some are integers). I'd like to transform some of the data permanently from percentage to integer only if the id number in another column matches. I want to do this as one of the applied data queries for this table, since the original data source will still show percentages for older data entries. We made the change in the original source (I don't have access to the database for this system, I can only export), so I'm stuck with those percentages.
Here's some JavaScript to show what I'm hoping to accomplish with a transform query in PowerBI:
var myId = 12345;
var myNum = "80%";
if (myId == 12345 && myNum.includes("%")) {
myNum = parseInt(myNum);
}
7 Replies
In Power Query Editor, add a custom column with
if [id] = 12345 and Text.Contains([value],"%") then Number.From([value]) else [value]Then change this new column to Text data type. In Text data type, it can remain percentages and integers in the same column. If you change it to other data types (e.g. Number), values in a column will be converted into the same format.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!I am still getting a % in the column when I apply the formula.
If I change it to a whole number, then 100% shows up as 1, and so does 80% (rounding up). So I added then (Number.From([grade]) * 100), but still get 1 for each.
Here is my complete formula:
if [myId] = 77 or [myId] = 80 and Text.Contains([myNum],"%") then (Number.From([myNum]) * 100) else [myNum])
Can you show some sample data and expected result in table format? Or show a screenshot of the current table in Power Query Editor without sensitive data? Without seeing the data it's difficult to tell where the problem is.
Best Regards,
Jing
