Results 1 to 7 of 7

Thread: Pivoting data by month column and summing the resulting months by row...problems

  1. #1
    Conjurer Ed Kelly's Avatar
    Join Date
    Jul 2016
    Posts
    164
    Articles
    0
    Excel Version
    2016

    Pivoting data by month column and summing the resulting months by row...problems



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Want to pivot a budget col by month https://www.screencast.com/t/CpVAh92msh. Once done want to sum each line from Jan to Dec to understand what total budget we are looking at. Using custom col to do this however the sum 12 month col only works for cols all of which are populated by a number if there is a null it treats the complete row as null https://www.screencast.com/t/KfaL8tnZE7. Have tried the normal - data types, changing null to 0...no effect. Am sure I am committing a school boy error here (wait let me be PC a school person's error, there thats better!).

    Any suggestions or better approaches?
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,373
    Articles
    45
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Quick fix... select all the columns that show nulls and do a replace null with 0. After you've done that, then run your SUM across the columns.

    We're a bit spoiled with Excel that it treats empty cells as zeros, but in PQ, null <> 0, so it can't do math against them. (It's a bit frustrating, to be honest!)

    Revised attached with it working now.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Conjurer Ed Kelly's Avatar
    Join Date
    Jul 2016
    Posts
    164
    Articles
    0
    Excel Version
    2016
    Ken, that was quick, totally works.

    Thank you, saved me bloody hours!

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,706
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by Ken Puls View Post
    We're a bit spoiled with Excel that it treats empty cells as zeros, but in PQ, null <> 0, so it can't do math against them. (It's a bit frustrating, to be honest!)
    If instead of:
    Code:
    each [201701]+[201702]+[201703]+[201704]+[201705]+[201706]+[201707]+[201708]+[201709]+[201710]+[201711]+[201712]
    we use List.Sum:
    Code:
    each List.Sum({[201712], [201711], [201710], [201709], [201708], [201707], [201706], [201705], [201704], [201703], [201702], [201701]}
    it appears you don't need to convert nulls to zero.

    It also means we can tweak the query to cope with different fiscal periods; here we are in 2020, where such hard-coded column headers would throw an error.
    If earlier on in the steps (before pivoting) you create a distinct list of values in the fiscal period column,:
    Code:
    rwhdrs = List.Distinct(#"Changed Type"[fiscalperiod])
    you'll have a list of headers you can use as one of the List.Sum arguments, although not as direct as:
    Code:
    Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,rwhdrs)
    (now not needed) where rwhdrs is a list of text, the List.Sum wants to see a list of column fields so (and this is where I feel sure there's a more direct route to achieve this) you could use:
    Code:
     each List.Sum(Record.FieldValues(Record.SelectFields(_, rwhdrs)))
    Note, this query took a minute or two to refresh, which could mean that it really could do with streamlining, or it may be due to my having more that 10 workbooks open, all with several Power Query queries in, amongst one or two other apps gobbling up my RAM.

    Anyway, it's in the attached.
    Attached Files Attached Files

  5. #5
    Conjurer Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    112
    Articles
    0
    Excel Version
    Excel 365
    Hi Ed,
    My proposal
    Code:
    let  Source = Excel.CurrentWorkbook(){[Name = "RD"]}[Content],
      #"Changed Type" = Table.TransformColumnTypes(
        Source, 
        {
          {"Dept", Int64.Type}, 
          {"Expense", Int64.Type}, 
          {"fiscalperiod", type text}, 
          {"budget", type number}
        }
      ),
      Headers = List.Sort(List.Distinct(#"Changed Type"[fiscalperiod])),
      #"Grouped Rows" = Table.Group(
        #"Changed Type", 
        {"Dept", "Expense", "fiscalperiod"}, 
        {{"budget", each List.Sum([budget]), type number}}
      ),
      #"Pivoted Column" = Table.Pivot(#"Grouped Rows", Headers, "fiscalperiod", "budget"),
      #"Added Custom" = Table.AddColumn(
        #"Pivoted Column", 
        "Annual Bgt $", 
        each List.Sum(List.LastN(Record.ToList(_), 12))
      )
    in
      #"Added Custom"

  6. #6
    Conjurer Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    112
    Articles
    0
    Excel Version
    Excel 365
    Quote Originally Posted by p45cal View Post
    Note, this query took a minute or two to refresh.....
    Try to use List.Buffer on rwhdrs step (should be much, much faster)

  7. #7
    Seeker masterelaichi's Avatar
    Join Date
    Feb 2019
    Posts
    13
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by Bill Szysz View Post
    Try to use List.Buffer on rwhdrs step (should be much, much faster)
    how does list.buffer or table.buffer work and when do you use them ? Could you please provide an example? I tried it on one of my queries and don't see any performance improvements. It was just as slow. I am sure I am using it incorrectly

Tags for this Thread

Posting Permissions

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