Forum Discussion

netteSJ's avatar
netteSJ
Frequent Visitor
3 years ago

Replicating excel formula

Hello guys,

Can anyone help me i have this excel formula that i need to replicate in power query. 

Here is my data table. 

 

 

 

 

 

 

My formula in excel :

 =IF(X3>=71%,"GOOD",IF(X3>=50%,"SATISFACTORY",IF(X3>=1%,"NEED IMPROVEMENT")))

 

 Really appreciate any help guys.

 

Need Help
Show and Tell
Tips and Tricks

3 Replies

  • vicky_'s avatar
    vicky_
    Super User
    3 years ago

    In power query: 

    = Table.AddColumn(Custom1, "Performance", each if [ColumnX] >= 71 then "GOOD" else if [ColumnX] >= 50 then "SATISFACTORY" else if [ColumnX] >= 1 then "NEED IMPROVEMENT" else null)

     

  • adityavighne's avatar
    adityavighne
    Continued Contributor
    3 years ago

    netteSJ 

    Add column 

    Performance =

    IF('Table'[toal score]>=71/100,"Good",IF('Table'[toal score]>=50/100,"Statisfactory","need"))
     
    try this. Make changes as per your categories you have.
     
    regards,
    Aditya 
  • netteSJ's avatar
    netteSJ
    Frequent Visitor
    3 years ago

    I forgot to mentioned that i am using below formula to get average performance for each Questions before doing the formula above

     

    Using formula 

    =COUNTIF(Data!I:I,K2) and theni just get the average