Results 1 to 1 of 1

Thread: PQ - How to get Total via Power Query

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

    Cool PQ - How to get Total via Power Query

    Source Result
    First Second Third Fourth Fifth Label First Second Third Fourth Fifth
    69
    76
    37
    40
    10
    Data
    69
    76
    37
    40
    10
    98
    51
    41
    59
    82
    Data
    98
    51
    41
    59
    82
    86
    90
    69
    72
    87
    Data
    86
    90
    69
    72
    87
    33
    50
    92
    27
    45
    Data
    33
    50
    92
    27
    45
    15
    72
    61
    41
    93
    Data
    15
    72
    61
    41
    93
    80
    46
    73
    93
    51
    Data
    80
    46
    73
    93
    51
    1
    89
    18
    64
    54
    Data
    1
    89
    18
    64
    54
    69
    42
    89
    75
    64
    Data
    69
    42
    89
    75
    64
    Total
    451
    516
    480
    471
    486

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Label = Table.AddColumn(Source, "Label", each "Data"),
        Move = Table.ReorderColumns(Label,{"Label", "First", "Second", "Third", "Fourth", "Fifth"}),
        AddTotal = Table.AddColumn(Move, "Custom", each "Total"),
        Group = Table.Group(AddTotal, {"Custom"}, {{"First", each List.Sum([First]), type number}, {"Second", each List.Sum([Second]), type number}, {"Third", each List.Sum([Third]), type number}, {"Fourth", each List.Sum([Fourth]), type number}, {"Fifth", each List.Sum([Fifth]), type number}}),
        Ren = Table.RenameColumns(Group,{{"Custom", "Label"}}),
        Append = Table.Combine({Move, Ren})
    in
        Append
    Last edited by sandy666; 09-30-2020 at 11:36 AM.
    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 - UnZip an use Content in Power Query
    By sandy666 in forum ETL PQ Tips and Tricks
    Replies: 0
    Last Post: 05-13-2020, 05:37 AM
  2. PQ - Running Total start over
    By sandy666 in forum ETL PQ Tips and Tricks
    Replies: 0
    Last Post: 05-09-2020, 11:05 PM
  3. a ongoing 30 day total
    By justme1052 in forum Excel Help
    Replies: 1
    Last Post: 05-23-2014, 08:10 AM
  4. no color in rows which have the Total or Grand Total
    By paul_pearson in forum Excel Help
    Replies: 4
    Last Post: 09-16-2013, 05:24 AM
  5. Replies: 8
    Last Post: 12-28-2012, 11:10 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
  •