Forum Discussion

EmmaDilemma's avatar
EmmaDilemma
New Member
3 years ago
Solved

How do I type this excel formula into Powerquery in Powerbi

Hi clever people,

 

I need to transfere this simple excel formula into a custom column in Powerquery in Powerbi . 

(J cell is Departure Date, I cell is Cover Start Date,BG cell is Month of Earning)

 

=IFERROR(IF(J470460<BG470460,0.2,SUM(0.2+SUM(BG470460-J470460)/SUM(J470460-I470460)*0.2)),0)

 

Can someone please tell me how I type this - I have got this far so far but am stuck at the SUM part....

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    So IF statements are written slightly different "if" "then" "else"  and you dont need to define a SUM like you do in Excel so try this...

     

    if ([Departure Date] < [Month of Earning] then 0.2 else ([Month of Earning] - [Departure Date]) / ([Departure Date] - [Cover Start])

     

    Let me know if that works for you.

     

    Thanks,

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    So IF statements are written slightly different "if" "then" "else"  and you dont need to define a SUM like you do in Excel so try this...

     

    if ([Departure Date] < [Month of Earning] then 0.2 else ([Month of Earning] - [Departure Date]) / ([Departure Date] - [Cover Start])

     

    Let me know if that works for you.

     

    Thanks,