Forum Discussion
Help convert Excel formula to use in Power Query
Hello everyone, I ask you to help convert the formula from excel to power guerry language m or language power bi. In the table associated with diseases, according to the criteria from column D to column D:J . I use this formula to create auto-calculated map legends with cities and countries. My formula looks like this:
=IF(K2=0,"no information",
IF(AND(MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2)<=K2,K2<ROUND(INT(AVERAGE(MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),AVERAGE(MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2)))),1-LEN(INT(AVERAGE(MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),AVERAGE(MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2))))))),
ROUND(INT(MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2)),1-LEN(INT(MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2))))&"-"&ROUND(INT(AVERAGE(MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),AVERAGE(MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2)))),1-LEN(INT(AVERAGE(MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),AVERAGE(MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2)))))),
IF(AND(ROUND(INT(AVERAGE(MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),AVERAGE(MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2)))),1-LEN(INT(AVERAGE(MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),AVERAGE(MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2))))))<=K2,K2<ROUND(INT(AVERAGE(MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2))),1-LEN(INT(AVERAGE(MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2)))))),
ROUND(INT(AVERAGE(MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),AVERAGE(MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2)))),1-LEN(INT(AVERAGE(MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),AVERAGE(MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2))))))&"-"&ROUND(INT(AVERAGE(MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2))),1-LEN(INT(AVERAGE(MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2))))),
IF(AND(ROUND(INT(AVERAGE(MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2))),1-LEN(INT(AVERAGE(MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2)))))<=K2,K2<ROUND(INT(AVERAGE(AVERAGE(MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2)),MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2))),1-LEN(INT(AVERAGE(AVERAGE(MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2)),MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2)))))),
ROUND(INT(AVERAGE(MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2))),1-LEN(INT(AVERAGE(MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2)))))&"-"&ROUND(INT(AVERAGE(AVERAGE(MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2)),MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2))),1-LEN(INT(AVERAGE(AVERAGE(MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2)),MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2))))),
IF(ROUND(INT(AVERAGE(AVERAGE(MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2)),MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2))),1-LEN(INT(AVERAGE(AVERAGE(MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2)),MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2)))))<=K2,">"&ROUND(INT(AVERAGE(AVERAGE(MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2)),MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2))),1-LEN(INT(AVERAGE(AVERAGE(MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2),MINIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2)),MAXIFS(K:K,$C:$C,C2,$D:$D,D2,$E:$E,E2,$F:$F,F2,$H:$H,H2,$I:$I,I2,$J:$J,J2))))),"Error")))))
My function divides the column into several virtual areas according to the criteria, looks for the highs and lows and then finds the average between them, then writes the gap that includes the values.
Table:
Hi Yussufff23 ,
For the MINIFS and MAXIFS functions, please kindly refer to
Power Query Classic COUNTIFS | SUMIFS | AVERAGEIFS | MAXIFS | MINIFS Calculations [Simply Amazing]Or you can use the Group By tool to find the max and min dates, then merge those dates back to the original data. See the Query sheet in the attached file.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Yussufff23 ,
The Power Query functions/statements you need are as follows:
IF(X, Y, IF(A, B, C)) = if X then Y else if A then B else C
AND(X, Y) = X and Y
ROUND(X) / INT(X) = Number.Round(X) (use different arguments to adjust output)
AVERAGE(X) = List.Average(X)
MIN(X) = List.Min(X)
MAX(X) = List.Max(X)
LEN(X) = Text.Length(X)
Convert text (X) > Number = Number.From(X)
Convert number (X) > Text = Text.From(X)
Best of luck 👍
Pete
In my formula, I used MINIF and MAXIF functions
Hi Yussufff23 ,
For the MINIFS and MAXIFS functions, please kindly refer to
Power Query Classic COUNTIFS | SUMIFS | AVERAGEIFS | MAXIFS | MINIFS Calculations [Simply Amazing]Or you can use the Group By tool to find the max and min dates, then merge those dates back to the original data. See the Query sheet in the attached file.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
