Forum Discussion

power-bi-noob's avatar
power-bi-noob
Helper I
3 years ago

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 anot...
Need Help
v-jingzhang's avatar
v-jingzhang
Community Support
3 years ago

Hi power-bi-noob 

 

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!

 

power-bi-noob's avatar
power-bi-noob
Helper I
3 years ago

Here is my complete formula:

if [myId] = 77 or [myId] = 80 and Text.Contains([myNum],"%") then (Number.From([myNum]) * 100) else [myNum])

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    3 years ago

    Hi power-bi-noob 

     

    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

    • power-bi-noob's avatar
      power-bi-noob
      Helper I
      3 years ago

      Hi v-jingzhang ,

      Here's my query editor window with the query, followed by the results. Thanks!

       

       

      • v-jingzhang's avatar
        v-jingzhang
        Community Support
        3 years ago

        Hi power-bi-noob 

         

        I don't have problem with your formula. Below is my result. Can you show the [assignment_id] column along with the [grade] column? Do the top rows which have "%" in grade you show have the two ids you write in the if condition? 

         

        BR,

        Jing