+ Reply to Thread
Results 1 to 11 of 11

Powerquery Nested calculations (max, rank and deltas)

  1. #1
    Forum Contributor
    Join Date
    10-14-2020
    Location
    Shanghai
    MS-Off Ver
    O365
    Posts
    156

    Powerquery Nested calculations (max, rank and deltas)

    Hi

    i want to analyse some data where do have a date, the ID number (=ID of the device), and a mileage.
    Target is to see how many times one ID is listed. If they are multiple times listed I want to see the rank and the delta of the mileage between each single case

    I started to load the data into PQ and group them.

    1. Group by ID
    2. Sort the nested table by mileage
    3. Add index column to nested table
    4. Add delta mileage for nested table
    5. Expand table
    6. Remove columns and sort columns

    I struggle with step 2 and step 4, the rest is fine

    Question:
    How can i sort the nested table and how can i calculate the delta between each row in the nested table. If only one row in the nested table, the delta should be 0

    The original file is big, so hopefully there is a solution with not to much calculation needed

    thanks a lot for every help
    Attached Files Attached Files
    Last edited by hansolu; 08-01-2021 at 12:56 AM. Reason: solved

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Powerquery Nested calculations (max, rank and deltas)

    I don't know what you mean by 'delta'. What are you showing us in the workbook? The column headers in the PQ table don't match those in what I presume is your mock-up, so although it makes perfect sense to you, it doesn't to me.

    In what way are you struggling with steps 2 and 4?

    I cannot visualise what you are wanting here - sorry.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Contributor
    Join Date
    10-14-2020
    Location
    Shanghai
    MS-Off Ver
    O365
    Posts
    156

    Re: Powerquery Nested calculations (max, rank and deltas)

    Hi,

    thanks for having a look into and sorry for my bad explanations. Delta is just the difference between two rows of mileage. e.g. second case (of same ID) mileage minus first case mileage.

    Please Login or Register  to view this content.
    Step2 should sort the created nested table by mileage.
    Step4 is to make the calculation for the cases where i do have more then 1 case (meaning one ID with different mileages). My idea is if more then 1 case (Sequence of cases per ID >1) then take the mileage from the row and deduct the value from row Index-1
    Both steps are not working yet

    I modified the workbook a bit and think its more readable now.

    Hope this is more clear now

    thanks a lot
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Powerquery Nested calculations (max, rank and deltas)

    Still clear as mud (to me) - sorry!

    Explain the numbers in column F: I think I see how some are derived, but can't see the logic for the 0 returns.

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Powerquery Nested calculations (max, rank and deltas)

    For your delta, look at this link.

    https://p3adaptive.com/2018/10/compa...-query-part-1/
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  6. #6
    Forum Contributor
    Join Date
    10-14-2020
    Location
    Shanghai
    MS-Off Ver
    O365
    Posts
    156

    Re: Powerquery Nested calculations (max, rank and deltas)

    Hi,

    okay, so some more words as explanation. I do get a list with data for each day. Data is the date, a mileage number and an ID number. THe ID number is the unique number of on bike. Mileage is the mileage counter of the bike on the day of repair.
    Now there are cases where one bike come back several times. IN this case I do have the same ID on different days. Like below.
    Date Mileage ID
    7/2/2020 177 1237
    7/20/2020 210 1237
    7/11/2020 238 1237

    No I need to know how often one ID is in the list, if its more then 1 time I need the rank the returns sorted by the Mileage. Normally Mileage and date should return the same sorting results. I also want to know how much mileage is the difference between the first repair and the second repair (my delta)

    My issue in PQ is that the grouping by ID is fine, but the sort order inside the nested tables are mixed up.

    Step 2 sorting group by
    Therefore in step 2 i need to sort my column containing the table from the group by operation.

    Then I add an index column for the repairs time order which gives me the ranking of the repairs from 1st repair to n.th repair.

    step 4 or column F
    Then I need to do the step 4, calculate the mileage. If the "Sequence of cases per ID" is >=2 then take the Mileage [Sequence of cases per ID] - Mileage [Sequence of cases per ID] - 1)" and so so.
    If the Number of repairs is only 1 time, or its the first time of repair for the cases the bike is repaired multiple times, my caculation is not working, therefore 0
    In other words,
    Please Login or Register  to view this content.

    I will look into the delta explananion, thanks alansidman, but not today anymore as i need to go out now.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Powerquery Nested calculations (max, rank and deltas)

    Let us know how you get on.

  8. #8
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Powerquery Nested calculations (max, rank and deltas)

    Please try

    Please Login or Register  to view this content.
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    10-14-2020
    Location
    Shanghai
    MS-Off Ver
    O365
    Posts
    156

    Re: Powerquery Nested calculations (max, rank and deltas)

    Hi,

    yes, thats working as expected. Thanks a lot.
    I saw an issue in the example, the sorting by date and mileage was screwed up a bit.

    However, the solution is perfect.

    Just for my understanding, do you have a link or something where i can learn to understand the formula? I am not quiet sure about the LET Structure inside the grouped step and also what you are doing in c=.., specially why {List.Max({[Sequence]-2,0}.

    Would be great if i am able to understand for next time

    thanks a lot

  10. #10
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Powerquery Nested calculations (max, rank and deltas)

    Please Login or Register  to view this content.

    a => each group Table

    b => add Count row and sequence column to a

    The sequence starts from 1, but the record index starts from 0, therefor previous record index start from [Sequence]-2

    record index must be 0 or positive List.Max({[Sequence]-2,0}) give 0 or more

    b[Mileage]{List.Max({[Sequence]-2,0})} give previous Mileage or start mileage

  11. #11
    Forum Contributor
    Join Date
    10-14-2020
    Location
    Shanghai
    MS-Off Ver
    O365
    Posts
    156

    Re: Powerquery Nested calculations (max, rank and deltas)

    Hi,
    thanks a lot for the explanation. Its a bit more clear now. Hope i can use it next time i run into the same problem again.
    You helped me a lot, thx

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. rank calculations
    By sunnyganjoo in forum Excel General
    Replies: 2
    Last Post: 05-31-2016, 01:02 PM
  2. [SOLVED] Rank and Nested IF Formula - Help Please!
    By hokkaido19 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 02-26-2016, 11:17 AM
  3. [SOLVED] Nested IF with multiple calculations
    By KIMMEYER in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-25-2016, 12:22 PM
  4. Revenue Rank & Date Calculations
    By dlythgoe in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-12-2009, 06:22 AM
  5. Bonus calculations using Nested IF's
    By Irfan123 in forum Excel General
    Replies: 3
    Last Post: 07-17-2008, 05:05 AM
  6. Using Nested RANK functions
    By tuph in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-11-2006, 12:20 AM
  7. Nested IF's with Rank Problem
    By exutable in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-09-2005, 08:50 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1