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

[Ed Kelly]

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?

[Ken Puls]

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.

[Ed Kelly]

Ken, that was quick, totally works.

Thank you, saved me bloody hours!

[p45cal]

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:

each [201701]+[201702]+[201703]+[201704]+[201705]+[201706]+[201707]+[201708]+[201709]+[201710]+[201711]+[201712]

we use List.Sum:

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,:

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:

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:

 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.

[Bill Szysz]

Hi Ed,
My proposal

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"

[Bill Szysz]

Note, this query took a minute or two to refresh.....

Try to use List.Buffer on rwhdrs step (should be much, much faster)

[masterelaichi]

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

[p45cal]

Try to use List.Buffer on rwhdrs step (should be much, much faster)

Yes indeed, that did speed it up tremendously, thanks.

Incidentally, sorry for such a late reply - I didn't get notified at all that there was a reply even though I should have, and it was just by chance that I came across this thread again.