Power Query / DAX solution to Excel formulas (or if this is even appropriate)

[IR15H]

Hello,

I've been using Excel for years but have recently been seduced by the appeal of Power BI as a means of providing more 'visually friendly' reports for other users to view and analyse. Whilst I can generally get Excel to do what I want to do (possibly through horrendously inefficient formulas/code), Power BI is just boggling my mind at the moment.

The issues is such; I have a large data source, that I have no ability to change, that's updated weekly and I download. I've currently written a macro that I run on each download that basically adds a load of additional columns containing formulas - I then save the Excel file and refresh my data source in Power BI, which makes all the visuals look pretty. However, I feel this is essentially a cop out of using Power BI fully - and that the 'proper' solution (given that I can't change the original data source) would be import the unedited report into Power BI directly and then use Power Query / DAX to alter/add to the data as required.

My Excel data is as below (which I sort by Column F, then R and then S, before my applying formulas in columns V-AD);

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]F[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[TD]AA[/TD]
[TD]AB[/TD]
[TD]AC[/TD]
[TD]AD[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Employee No.[/TD]
[TD]On Cal[/TD]
[TD]Roster Date[/TD]
[TD]Shift Start[/TD]
[TD]Shift Finish[/TD]
[TD]Shift Paid[/TD]
[TD]On Call?[/TD]
[TD]Rest[/TD]
[TD]<12hrs[/TD]
[TD]>12hrs[/TD]
[TD]7 days[/TD]
[TD]>60hrs[/TD]
[TD]>72hrs[/TD]
[TD]Consecutive[/TD]
[TD]13+ shifts[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Number/Text[/TD]
[TD]Y/N[/TD]
[TD]DD-MM-YY[/TD]
[TD]DD-MM-YY HH:MM:SS[/TD]
[TD]DD-MM-YY HH:MM:SS[/TD]
[TD]H.MM[/TD]
[TD]=IF(O2="Y",IF(SUMPRODUCT(($F$2:$F2=F2)*($P$2:$P2=P2))>1,0,1),0)[/TD]
[TD]=IF(F2<>F1,23.99,IF(R2="",23.99,IF(S1="",23.99,(R2-S1)*24)))[/TD]
[TD]=IF(AND(W2<12,W2>0),1,0)[/TD]
[TD]=IF(W2=0,IF(U2+U1>12,1,0),IF(U2>12,1,0))[/TD]
[TD]=SUMIFS(U:U,F:F,F2,P:P,"<="&P2,P:P,">="&P2-6)[/TD]
[TD]=IF(Z2>60,IF(P2=P3,0,1),0)[/TD]
[TD]=IF(Z2>72,IF(P2=P3,0,1),0)[/TD]
[TD]=IF(F2=F1,IF(R2<>"",IF(P2=P1,AC1,IF(P2-P1=1,AC1+1,1)),0),0)[/TD]
[TD]=IF(AC2>13,IF(P2=P3,0,1),0)[/TD]
[/TR]
</tbody>[/TABLE]

The primary reason for needing all the additional formulas is there can be multiple entries for the same employee number (F), on the same date (P) and its important to distinguish if these entries and consecutive or not (based on start start/finish times (R/S)) but at the same time not double counting those entries for other measures. What I want to know/learn how to do is convert the above formulas to DAX and/or Power Query equivalents, if possible? I've attempted this a few times before by googling/reading stuff but usually just end up confusing myself even more.

The above is actually one of three sheet I do similar things to, but I figure if I can get the above cracked (or even just some of it) I'll be able to figure out the rest from there.

Any help appreciated and if you need any more info please let me know.

 

[bobby_smith]

You can use Power Query feature found under Add Column -> then select Custom Column. All the if formulas you have in column V to Y can be done using the custom column feature.
This website gives good example on how to use power query if function: https://www.myonlinetraininghub.com/power-query-if-statements

 

[IR15H]

Hi,

Thanks for your response and link, I managed to get some of the more simpler formulas to work (and can probably figure out the rest when I get a bit more time).

I think the main thing that threw me was that some of my Excel formulas have a relative reference to a row above or below... and I was trying to do things like;

if [A] - 1 = [B] then "A - 1 = B" else "A-1 <> B"

Which just wasn't working and then going round in circles by trying to use 'Excel logic'.

I've since found this and this link, which seem to suggest the way to do it is via some form of index columns. Again, hopefully when I get a bit more time, I can follow through and achieve what I'm after.