PDA

View Full Version : Pivoting data by month column and summing the resulting months by row...problems



Ed Kelly
2017-04-25, 04:44 PM
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
2017-04-25, 05:29 PM
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
2017-04-25, 05:55 PM
Ken, that was quick, totally works.

Thank you, saved me bloody hours!