Convert SharePoint List Calculated column Formula to Power Query Custom Column

[deb_a]

Hello Everyone,

I have a few SharePoint lists where I am using some calculated columns to get the monthly FTE of project assignments. Currently I have 1 column in the SharePoint list for every month of the year (e.g. Jan 2022, Feb 2022 and so on).

I would like to find a way to create these columns in the PowerBI Desktop instead of the SP lIst. Can any one please help me convert the below Sharepoint formula to Power Query Custom Column Formula. Below sample formula is used to calculate the FTE for Jan 2020. The below formula uses values from 3 other columns namely : "No. of hours", "Actual Start date", "Actual End date".

=(IFERROR(([No. of hours]/(IF(([Actual End date]-[Actual Start date]),([Actual End date]-[Actual Start date]),1)))*(DATE(2020,1,1)=MEDIAN(DATE(YEAR([Actual Start date]),MONTH([Actual Start date]),1),DATE(YEAR([Actual End date]),MONTH([Actual End date])+1,1)-1,DATE(2020,1,1)))*(IF([Actual End date]=[Actual Start date],1,(MIN([Actual End date],DATE(2020,1,31))-MAX([Actual Start date],DATE(2019,12,31))))),""))/170

Thanks in Advance.

[ImkeF]

Hi @deb_a ,
this looks doable in Power Query.
But please format the code so I can understand the logic easier.

[Greg_Deckler]

@deb_a That formula would almost perfectly translate to DAX. More difficult with Power Query but maybe someone like @ImkeF can assist.