Converting Excel formula to Power Query

[SJHALANI]

Hi,

I had an excel which had lots of formula and thus it made its working very slow. Now I am in the process of migrating my excel formulas to Power query. I have the below formula in Excel which I am not sure how to convert to PQ formula. Any help would be greatly appreciated.

=IFERROR(@IF([@key]>0,SWITCH([@[Fbase_rate_monthly_fraction]],"Based on Fixed Hours", [@[Total Hours]]<=[@[Ffixed_hours]],"Based on Net Hours", [@[Total Hours]] <= [@[Fnet_hours]],"Based on Total Hours", [@[Total Hours]] <= [@[Ftotal_hours]],"N/A", [@[Total Hours]] <=[@[Ftotal_hours]],FALSE),""),"")

TIA!

[m_dekorte · Accepted solution]

Hi @SJHALANI 

Give this a go, it assumes that all field names (everything in square brackets) correspond to column names in your table. Add a custom column and enter this in the formula section:

try
  if [key] > 0 then {
    [Total Hours] <= [Ffixed_hours], 
    [Total Hours] <= [Fnet_hours], 
    [Total Hours] <= [Ftotal_hours], 
    [Total Hours] <= [Ftotal_hours], 
    false
  }{ List.PositionOf(
      {
          "Based on Fixed Hours", 
          "Based on Net Hours", 
          "Based on Total Hours", 
          "N/A", 
          false
      }, 
      [Fbase_rate_monthly_fraction]
  )}
else ""
otherwise ""

I hope this is helpful