• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Subtract Distinct Rows|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Subtract Distinct Rows|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QuerySubtract Distinct Rows
sp_PrintTopic sp_TopicIcon
Subtract Distinct Rows
Avatar
ExcelNovice2020
Member
Members
Level 0
Forum Posts: 58
Member Since:
February 8, 2020
sp_UserOfflineSmall Offline
1
May 2, 2022 - 4:07 am
sp_Permalink sp_Print

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.    

Avatar
austris bahanovskis
Member
Members
Level 0
Forum Posts: 14
Member Since:
July 13, 2021
sp_UserOfflineSmall Offline
2
May 4, 2022 - 1:26 am
sp_Permalink sp_Print

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 🙂

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 442
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
3
May 4, 2022 - 5:15 pm
sp_Permalink sp_Print

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.

Avatar
ExcelNovice2020
Member
Members
Level 0
Forum Posts: 58
Member Since:
February 8, 2020
sp_UserOfflineSmall Offline
4
May 4, 2022 - 11:09 pm
sp_Permalink sp_Print

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.  

   

      

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 442
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
5
May 4, 2022 - 11:35 pm
sp_Permalink sp_Print

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:

Screenshot-2022-05-04-153138.pngImage Enlarger

Also uploaded a revised file.

sp_PlupAttachments Attachments
  • sp_PlupImage Screenshot-2022-05-04-153138.png (41 KB)
Avatar
ExcelNovice2020
Member
Members
Level 0
Forum Posts: 58
Member Since:
February 8, 2020
sp_UserOfflineSmall Offline
6
May 5, 2022 - 4:00 am
sp_Permalink sp_Print

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.  

Avatar
ExcelNovice2020
Member
Members
Level 0
Forum Posts: 58
Member Since:
February 8, 2020
sp_UserOfflineSmall Offline
7
May 5, 2022 - 7:08 am
sp_Permalink sp_Print

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. 

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 442
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
8
May 5, 2022 - 1:25 pm
sp_Permalink sp_Print

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.

Avatar
ExcelNovice2020
Member
Members
Level 0
Forum Posts: 58
Member Since:
February 8, 2020
sp_UserOfflineSmall Offline
9
May 5, 2022 - 10:22 pm
sp_Permalink sp_Print

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.

    

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 442
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
10
May 5, 2022 - 10:38 pm
sp_Permalink sp_Print

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.

Avatar
ExcelNovice2020
Member
Members
Level 0
Forum Posts: 58
Member Since:
February 8, 2020
sp_UserOfflineSmall Offline
11
May 6, 2022 - 1:01 am
sp_Permalink sp_Print

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.  

  

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 442
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
12
May 6, 2022 - 1:14 am
sp_Permalink sp_Print

Can't explain it either. Sorry!

Avatar
ExcelNovice2020
Member
Members
Level 0
Forum Posts: 58
Member Since:
February 8, 2020
sp_UserOfflineSmall Offline
13
May 6, 2022 - 2:06 am
sp_Permalink sp_Print

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.   

Avatar
ExcelNovice2020
Member
Members
Level 0
Forum Posts: 58
Member Since:
February 8, 2020
sp_UserOfflineSmall Offline
14
May 6, 2022 - 2:24 am
sp_Permalink sp_Print

Just a quick note here, incorporating RoundingMode.Up with the Number.Round seems to solve my rounding issue.  

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 442
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
15
May 6, 2022 - 4:38 am
sp_Permalink sp_Print

Great! Well done.

Avatar
austris bahanovskis
Member
Members
Level 0
Forum Posts: 14
Member Since:
July 13, 2021
sp_UserOfflineSmall Offline
16
May 6, 2022 - 9:06 pm
sp_Permalink sp_Print

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)

Avatar
ExcelNovice2020
Member
Members
Level 0
Forum Posts: 58
Member Since:
February 8, 2020
sp_UserOfflineSmall Offline
17
May 7, 2022 - 10:51 pm
sp_Permalink sp_Print

Thank you for pointing that out the PQ rounding default. 

To be certain, with this particular situation, wouldn't RoundingMode.Up accomplish the same task as RoundingMode.AwayFromZero?

Avatar
austris bahanovskis
Member
Members
Level 0
Forum Posts: 14
Member Since:
July 13, 2021
sp_UserOfflineSmall Offline
18
May 8, 2022 - 9:19 pm
sp_Permalink sp_Print

yes, it would - so, all good there 🙂

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: dan conner, Emma Klonowski
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 204
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Brett Dryland
Saeed Aldousari
Bhuwan Devkota
Kathryn Patton
Maria Conatser
Jefferson Granemann
Glen Coulthard
Nikki Fox
Rachele Dickie
Raj Mattoo
Forum Stats:
Groups: 3
Forums: 24
Topics: 6222
Posts: 27291

 

Member Stats:
Guest Posters: 49
Members: 31912
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.