Summing by Quarter (dates are in header row)

[pdvsa]

Hello Experts,

How can I sum this data by quarter? Dates are in row 1 as a header. I would like to have a pivot and date filter and sum by quarter but I seem to remember that having dates in a header row creates issues.

Beneficiary

Issuing Entity

All In Rate

4/1/2020

5/1/2020

6/1/2020

7/1/2020

8/1/2020

9/1/2020

10/1/2020

11/1/2020

12/1/2020

1/1/2021

2/1/2021

3/1/2021

4/1/2021

5/1/2021

Co 1

IE 1

0.015​

250​

250​

250​

250​

250​

250​

250​

250​

250​

250​

250​

250​

250​

250​

Co 2

IE 2

0.0177​

2950​

2950​

5930​

5930​

2950​

2950​

3393​

3393​

3393​

8260​

8260​

8260​

8260​

8260​

Co 3

IE 3

0.018​

0​

0​

0​

7367​

7367​

7367​

7367​

7367​

7367​

7367​

7367​

7367​

7367​

5084​

Co 4

IE 4

0.018​

9513​

9513​

9513​

9513​

9513​

9513​

9513​

9513​

9513​

11099​

11099​

11099​

11099​

11099​

Co 5

IE 5

0.018​

6342​

6342​

6342​

6342​

6342​

6342​

6342​

6342​

6342​

7928​

7928​

7928​

7928​

7928​

Co 6

IE 6

0.018​

8309​

8309​

8309​

8309​

8309​

8309​

8309​

8309​

8309​

9693​

9693​

9693​

9693​

9693​

Co 7

IE 7

0.018​

5539​

5539​

5539​

5539​

5539​

5539​

5539​

5539​

5539​

6924​

6924​

6924​

6924​

6924​

Co 8

IE 8

0.018​

178​

178​

178​

178​

178​

178​

178​

178​

178​

208​

208​

208​

208​

208​

Co 9

IE 9

0.018​

119​

119​

119​

119​

119​

119​

119​

119​

119​

149​

149​

149​

149​

149​

Co 10

IE 10

0.025​

208​

208​

208​

208​

208​

208​

208​

208​

208​

208​

208​

208​

208​

208​

Co 11

IE 11

0.018​

1006​

1006​

1006​

1006​

1006​

1006​

1006​

1006​

1006​

1006​

1006​

1006​

1006​

1006​

Co 12

IE 12

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

Co 13

IE 13

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

Co 14

IE 14

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

Co 15

IE 15

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

Grateful for your assistance.

 

[alansidman]

Cannot manipulate your data. Suggest you re upload using XL2BB function. See instructions in my signature.

 

[Alex Blakenburg]

I had similar issues to alan, but I am sure that Power Query will do what you need, which it to unpivot the data.

Start with converting your data into a table:

• Select any cell in your data range
• Either Insert > Table (3rd button from the left ) OR Ctrl + T
• Please the give the Table a meaningful name (UnderTable Design > White box on the far left)
  (I normally use a prefix such as tbl_ to that when I can't remember the table name later I just need to type tbl and I get a list of my tables)

Once you have done that,

• click anywhere in the table
• Data > From Table Range (2nd lot of buttons from the left)
• This will take you to the power query editor
• On the right side, if your 2nd step is Changed Type, delete that step
• Select all the column to the left of your first date column (shift + select)
• Transform > Unpivot Columns > Unpivot Other Columns
• Change all the column type (by clicking on the type characters to the left of the column name)
  Image of how mine looks below.
• Rename column to what you want them to be.
• Close and Load
• Build you pivot using the newly created (by PQ) table

If you add or change data in your original table and refresh the Query it will automatically change.

My data types.

 

[pdvsa]

Alex, thank you very much. That worked perfectly!

 

[Alex Blakenburg]

Thanks for letting me know, Glad we were able to help.