Pivot table from PQ - help improve my solution

[henryg]

I have a cross-tabulated pivot table created via Excel Powery Query from data exported from an accounting program; it works but I feel it is clunky and could be improved. The major issue is that the financial year is 30 April annually.

So I used PQ to assign an accounting year to each data date, eg 1 Jun 2023 would be in FY 2024, and similarly a financial year month number, and I also extract the month name. I end up with

I then hide the month number row which gives me exactly what I want, but I feel it could be done more elegantly. So what do you think?

 

[pjmorris]

Hi Henry, for obvious reasons you would be better posting this in the Power Tools section. [Mod note: thread has been moved]

That said, my advice would be to create a calendar either in PQ or in PowerPivot (I prefer to create my own in PQ) you can then assign the accounting blocks as you wish.

HTH

 

[henryg]

Hi Henry, for obvious reasons you would be better posting this in the Power Tools section. [Mod note: thread has been moved]

That said, my advice would be to create a calendar either in PQ or in PowerPivot (I prefer to create my own in PQ) you can then assign the accounting blocks as you wish.

HTH

Not sure how to do/use this. I have Googled, but little the wiser. It's not a problem to allocate dates to financial years and months, so I'm unsure how a calendar will help - but then I don't really understand.

I was thinking of a formulaic solution using new Excel 365 functions - may be beyond me, but worth a go. No need to refresh, but also no drill down. I may just settle for "inelegant"

 

[GraH]

With a calendar table you'd have a dimension "fiscal/fin year". That's the suggestion of Peter.
No need for a calculation, simply add the dimension from the Power Pivot. Please never create the cross tab report via PQ. That's a Pivot's job.

 

[henryg]

Sorted; pun intended.

I forgot that I had a custom list set up, so all I had to do was go into the header row field sort option, dig down a bit and set the sort by custom list. I'm pretty sure I must have done this in the past, but perhaps lost it on an update, as my recollection was that the sort order used to be how I wanted it.

All's well that end well. A few hours spent, but not wasted as I have re-learnt something.