question

vikasdk-2732 avatar image
0 Votes"
vikasdk-2732 asked vikasdk-2732 commented

Sum of range of columns in power query

I am new to power query and need some help. I have 25 columns and 10k rows table with value data in it. I want to create custom column which will result the sum of specific range of columns. i.e. sum of the data from 3rd column to 10th column. I wish to have this formula dynamic, means the range of number of columns (above I have selected as 8 ) will vary every month. I could achieve it thru "Offset" function in excel but not able to do so in Power query. Please advice.

power-query-not-supported
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

HerbertSeidenberg-6295 avatar image
0 Votes"
HerbertSeidenberg-6295 answered vikasdk-2732 commented

Excel 365 Pro Plus with Power Pivot and Power Query.
Sum selected columns.
UnPivot with PQ, then pivot with PP.
Select with Slicer and TimeLine.
Way beyond beginner.
https://www.mediafire.com/file/ydv3s5i13kl78wl/09_02_21.xlsx/file
https://www.mediafire.com/file/g15ywgr8wuuqbo0/09_02_21.pdf/file

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Thanks for the response.

I think I had provided very less information regarding what I am trying to achieve and my apologizes for the same.

There are 30 columns in the table and last 24 columns are month columns. Extreme last 12 columns are for "Actual" and prior to it the 12 columns are for "Estimated". The titles of these columns are like "Actual -1", "Actual -2" and so on.... similar for "Estimated-1" and so on. I run this report monthly and need the sum of the values from "Jan to current month". And I need it for both set of columns "Estimated" and "Actual". The rows in the table are more than 10k. I am trying to have customized column where in I can use current month number so that it will calculate from Jan to Aug in this month and for next month it will calculate from Jan to Sept. Currently I am using "Offset function" in my excel report and it works okay. Hope this is clear.

0 Votes 0 ·

Don't fix what ain't broken.
Needed from you: Complete 3Mb shared file.
Needed by you: Three months of PQ/PP training.
Needed by me: Patience.

0 Votes 0 ·
vikasdk-2732 avatar image vikasdk-2732 HerbertSeidenberg-6295 ·

Hi....Thanks for the response and feedback. I will work on the sheet and will revert if need more support. Thanks.

0 Votes 0 ·