Results 1 to 1 of 1

Thread: PQ - Sum values for each label from defined range of columns

  1. #1
    Sir Moderator sandy666's Avatar
    Join Date
    May 2018
    Posts
    239
    Rep Power
    7

    Cool PQ - Sum values for each label from defined range of columns



    Code:
    let From = Excel.CurrentWorkbook(){[Name="From"]}[Content][From]{0},
        To = Excel.CurrentWorkbook(){[Name="To"]}[Content][To]{0},
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        UOC = Table.UnpivotOtherColumns(Source, {"Labels"}, "Attribute", "Value"),
        Type = Table.TransformColumnTypes(UOC,{{"Attribute", Int64.Type}}),
        Select = Table.AddColumn(Type, "Select", each List.Select({[Attribute]}, each (_ >= From and _ <= To))),
        Expand = Table.ExpandListColumn(Select, "Select"),
        FilterNull = Table.SelectRows(Expand, each ([Select] <> null))
    in  Table.Group(FilterNull, {"Labels"}, {{"Sum", each List.Sum([Value]), type number}})
    Note: cannot post data because it exceeds 15000 characters
    Last edited by sandy666; 08-24-2020 at 04:46 PM.
    sandy
    I know you know but I forgot my Crystal Ball and don't know what you know also I may not remember what I did weeks, months or years ago so answer asap. Thinking doesn't hurt

Similar Threads

  1. PQ - Sum columns with null values
    By sandy666 in forum ETL PQ Tips and Tricks
    Replies: 0
    Last Post: 06-10-2020, 09:25 PM
  2. Replies: 5
    Last Post: 07-30-2014, 07:51 PM
  3. Sum Between Values Within The Same Range
    By msiyab in forum Excel Help
    Replies: 5
    Last Post: 02-17-2014, 12:30 AM
  4. Replies: 2
    Last Post: 05-14-2013, 01:02 AM
  5. Excel Macro to Sort Data if a value changes in defined range
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 4
    Last Post: 09-05-2012, 10:31 AM

Posting Permissions

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