+ Reply to Thread
Results 1 to 10 of 10

Running total by group within advanced editor

  1. #1
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    615

    Running total by group within advanced editor

    Hi,

    I've managed to do running totals by grouped data in power query by performing the process on one of the grouped tables then converting to a function
    and invoking on within the original.

    So,
    < (GroupTotal) =>
    let
    Index = Table.AddIndexColumn( GroupTotal , "Index" , 1, 1, Int64.Type),
    buffer = Index[Units] ,
    Output = Table.AddColumn ( Index ,"RTC" , each List.Sum (List.FirstN( buffer , [ Index ] ) ) )
    in
    Output >

    Rather than having this a function in itself, I've seen a couple of tutorials where it is added in the advanced editor to the grouped data,
    but I keep getting it wrong.
    Any reading recommendations welcome, I find a lot of YouTube vids a bit hard as if not familiar with syntax it's easy to miss somthing.
    I've attached the sheets I've been playing around with.

    Richard
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,063

    Re: Running total by group within advanced editor

    What is the desired output? Please add a few rows of what you want to see...
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Running total by group within advanced editor

    You need to name the function step within the query. So your query might end up something like:

    Please Login or Register  to view this content.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,063

    Re: Running total by group within advanced editor

    For fun... I'm determined to master dynamic arrays... so this (might be) a fully dynamic version of what you wanted... But it might not be, as you didn't answer my Q in Post 2.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    615

    Re: Running total by group within advanced editor

    Hi,
    thanks I'll try that.

    RD

  6. #6
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    615

    Re: Running total by group within advanced editor

    Hi Glenn,
    the out put is the grouped rows, as in the attached file, it's just that I don't want to achieve this through invoking a custom function but rather embed the function with in the code through advanced editor.

    Richard.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,063

    Re: Running total by group within advanced editor

    No file attached.

  8. #8
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    615

    Re: Running total by group within advanced editor

    Hi Oly,
    I've been doing some online reading and I wonder if you can help, when creating a query by having performed the steps and then manipulate the code in the editor if usually start with stating a parameter ( x ) then the go too => and on the next line 'let' and then the body text starts.
    I other case the when tying one in from scratch it starts within the 'let' and 'in' and then there are cases where as above there is a let followed by another let and two 'in' , and I find it a bit confusing.
    Can you direct me to a good source of information.
    Richard.

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

    Re: Running total by group within advanced editor

    Different method

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

  10. #10
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Running total by group within advanced editor

    Quote Originally Posted by Dicken View Post
    Can you direct me to a good source of information.
    Here's a couple of decent starting points:

    https://blog.crossjoin.co.uk/2016/05...d-power-query/

    https://docs.microsoft.com/en-us/pow...let-expression

+ 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. Replies: 0
    Last Post: 10-14-2021, 12:21 PM
  2. Macro not running if VBA editor is not open
    By lualpa in forum Outlook Programming / VBA / Macros
    Replies: 4
    Last Post: 10-05-2019, 11:15 AM
  3. Running total after payments running total on excel
    By tmagplayr in forum Excel General
    Replies: 7
    Last Post: 09-26-2017, 02:01 PM
  4. Replies: 8
    Last Post: 07-19-2017, 12:06 PM
  5. Replies: 14
    Last Post: 01-12-2017, 01:41 PM
  6. code works running in visual basic editor but not when running from Excel macro
    By smporco in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-06-2016, 05:44 PM
  7. [SOLVED] Advanced File Editor
    By John in Boston in forum Excel General
    Replies: 2
    Last Post: 02-03-2006, 10:45 AM

Tags for this Thread

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