Log In
Register
Home
February 8, 2020
Offline
Please see attached.
While I can create formulas within Excel itself to complete my task, I am trying to learn how to do this within power query.
What I would like to do is, between each of the two "matchups," take the higher H2H Score and subtract the losing amount to have a "points behind" column. Thus, in the attached example, within Excel itself, I would normally have the following formulas for the first matchup in cells C2 and C3 was follows:
C2: =IF($B2>$B3,"0",$B2-$B3)
C3: =IF($B3>$B2,"0",$B3-$B2)
This would then continue for each matchup. I tried indexing with a merge but that did not accomplish my task. I am stuck. Any recommendations would be help. Thanks.
July 13, 2021
Offline
See attached - tab [rslt (not elegant)] col [MyCalc].rnWord of caution - there are loads of MUCH BETTER way of doing that than the way I did it. The point is I wanted to practice with List.Acumulate and therefore did this long-winded way. But it seems to work 🙂
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
As a variant, I created a new smaller table (starting with a reference to the larger table) with just the Team, H2H Score and PointsBehind, and assuming you would want to keep your table with empty rows between the "match-ups". See attached.
If you want to integrate it in the larger table, I trust you can achieve that yourself.
February 8, 2020
Offline
Thank you both. Just an initial look, while it does achieve the objective, there are a few steps I am not familiar with so want to review more to try and learn the concept as it may tie into my final column.
Along the very same lines, my final column, I need it to calculate a "bonus" for an opponent who achieves a "H2H "Score" of 300 or more. Thus, in the examples provided, if I took the Assassins and Mullz's Malcontents matchup, if Assassins scored 300 or more, I need a column labeled "Opponent Bonus" that calculates a bonus based upon 1.5% any amount over 300 of their opponent.
As an example. Assassins H2H Score is 400. I need a column showing a bonus of 1.5 (100*1.5%) for Mullz's Malcontents.
Mullz's Malcotents H2H Score is 500. I need the bonus to show 3 (200*1.5%) for Assassins.
It does not make sense but what I am to setup. Thanks.
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
Added a formula similar to PointsBehind for the Bonus. Tested it by hard-coding some H2H values, although they were overwritten upon refresh. But I did get the 1.5 and 3 as you described.
The entire script now looks like this:
Also uploaded a revised file.
February 8, 2020
Offline
Both appear to work very well. The problem I have is, I am "good" with the understanding once I break this out until the code within the "if" / "then" statement. Yes, it works; I just wish I understood why but I just am not experienced enough.
Thank you very, very much. I had no idea how to get this accomplished. I am amazed at the knowledge here. Again, thank you.
February 8, 2020
Offline
Quick note here as I finally could test the last part with the "bonus" applied for anything over 300. There appears to be some "rounding" issues as one example for a score of 381.833 which should return a "bonus" of 1.227 (81.833 * 1.5%) is actually returning 1.228. Seems like power query rounds down for "5" instead of up. For example, the actual number here is 1.227495 and rounding to the thousand (i.e. 3 spots) should be 1.227 instead of 1.228.
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
Could it be that the 81.833 in fact is 81.8333333333333333333? 1.5% of the latter equals 1.22750, which would round to become 1.228. Not sure how accurate PQ works in these circumstances, though.
February 8, 2020
Offline
Looks that way. Is there a way, within the "bonus" calculation formula to add a "round" feature to use the figure of 81.833 to multiply to the 1.5% to obtain a figure of 1.227495, which I could round to 1.227?
I know how to take the current formula, remove the 1.50% portion of the calculation, generate the 1.227495 result and then round that number to achieve the expected resulted (rounded to the thousand position) but was not sure if a way to add it to the current formula as is, saving some steps.
Regardless, I can make this work.
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
You can wrap the relevant parts of bonus calculation in the Number.Round function, like this:
( Number.Round ( PtsBehind[H2H Score]{[Index]+1} , 3 ) - 300 ) * 0.015
Tried by replacing a value to 381.8333333333. The formula returned 1.227495 and then rounded the column to 3 decimals to end up with 1.227.
February 8, 2020
Offline
For the most part, this works. At least on the one number I was getting the rounding error. However, for the life of me, I have no idea why power query is taking a number of .4125 and when being told to round to the third digit, it becomes .412.
If I look at the O's and Killer B's matchup, the 27.500 (excess over 300) * 1.5% returns .4125. I would think that would round to .413. It does outside of Power Query.
At this point, the majority is working. I am just not "sharp" enough to understand some of the little differences. I never will. Frustrating.
I appreciate all the help here. The "hard part" you got me through.
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
Can't explain it either. Sorry!
February 8, 2020
Offline
No worries. All good help here and very much appreciated. I would have never figured out the methods provided here for this subject. The rounding part, another day/topic. Thanks to all.
February 8, 2020
Offline
Just a quick note here, incorporating RoundingMode.Up with the Number.Round seems to solve my rounding issue.
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
Great! Well done.
July 13, 2021
Offline
re: rounding - PQ defaults to banker's rounding (to nearest even number).
Use the 3rd param in the rounding to get the 'normal' result:
Number.Round(_,1,RoundingMode.AwayFromZero)
1 Guest(s)
