+ Reply to Thread
Results 1 to 3 of 3

Power query return total value of grouped columns

Hybrid View

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

    Power query return total value of grouped columns

    Hello,

    I have some grouped data, and I want to return the total of the grouped columns to another columns so I can perform another operation.,
    I've used Add custom column

      Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Grouped Rows" = Table.Group(Source, {"Sales Rep"}, {{"Tsales", each List.Sum([Sales]), type number},
    { "Products Sold " , each  Text.Combine ( 
     List.Distinct( [Product]), ", " )   }} ),
        #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Tsales])
    in
        #"Added Custom"
    and this does work, but what i'd like to do is bring jut add it to the grouping ;

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Grouped Rows" = Table.Group(Source, {"Sales Rep"}, {{"Tsales", each List.Sum([Sales]), type number},
    { "Products Sold " , each  Text.Combine ( 
     List.Distinct( [Product]), ", " ) } ,{ "Tsales2" , each [Tsales] }} )
    in
        #"Grouped Rows"
    But this does not recognise the Column Tsales.
    I've attached small workbook with 3 queries 2 of which work but aren't quite what I want and one which doesn't but would like to get working.

    Richard.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Power query return total value of grouped columns

    Maybe like this:

    filter on value => Top 5

    See the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

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

    Re: Power query return total value of grouped columns

    Thanks,

    RD

+ 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. [SOLVED] Need help- Power Query Merge function return null value!
    By marvin-tech in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-14-2022, 12:23 PM
  2. [SOLVED] Running total in power query with criterion
    By Dicken in forum Office 365
    Replies: 6
    Last Post: 01-10-2022, 09:33 AM
  3. Power Query Grouped Running Total
    By sle7en in forum Excel General
    Replies: 2
    Last Post: 02-13-2021, 06:30 PM
  4. [SOLVED] ado truncates return value Power query does not
    By Steveapa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-18-2020, 09:24 PM
  5. Replies: 4
    Last Post: 02-17-2020, 06:03 AM
  6. Custom Permutations Column in Power Query from two columns in the same Query
    By PaintPaddy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-11-2019, 02:48 PM
  7. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM

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