Forum Discussion

SJHALANI's avatar
SJHALANI
Helper I
2 years ago
Solved

Converting Excel formula to Power Query

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!

Need Help
  • m_dekorte's avatar
    m_dekorte
    2 years ago

    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

1 Reply

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    2 years ago

    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