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
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

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

        Hi v-jingzhang , I also created a separate table with a toy example and it seemed to work fine. It just doesn't work with my production data for some odd reason.