Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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
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