Applying Cashflow Categories to transactions

[DickyMoo]

Hi all,

I have a list of transactions that I would like to split up by cashflow category. These categories depend on combinations of:

- Account Code
- Journal Type
- Cost Centre
- Transaction Reference

I have set up 4 lookup tables with the above data and merge them with the transactions table, but was wondering if someone could suggest a different approach.

I can't think of another way, other than using VBA to loop through and apply categories, but I feel sure that PQ will have the answer.

Thanks
Rich

ps I can attach files if necessary, but its really more of a general direction I'm looking for.

 

[Brian Smith]

Depending on how the data is structured you might be able to apply a series of classification rules using custom columns - in other words build in the logic of your look up tables to a query. This is conceptually the same as a VBA loop I think. However, that may not be better (and I suspect probably is not better) than setting up the merging of you tables in power query. You could also load the tables to power pivot and create the relationships between the tables that way and write the measures you need as they come up.

 

[DickyMoo]

Thanks Brian.

I've been thinking maybe I could create a separate query for each category, and then append the queries. Do you know if its possible to pull in the data from the database once, then split in PQ according to category, then append once categories applied?

Or am I making life too complicated?

Thanks again.

 

[Brian Smith]

I suppose complicated is in the eye of the beholder. If I'm understanding you correctly and you have a fact table with cash flows with linking ID's of some kind that can merge with dimension tables that describe the cash flows, then in my opinion it is easier to bring in the fact table from the data base and join it to the dimension tables. But to answer the question you asked, no, there's no reason you can't bring in the data, modify it with as many queries on the same data as you like and then append those queries.

 

[jafa1970]

Can you give some insight into the business rules that apply to define the categories? You indicated the category is based on 4 different fields.

If that business logic is very static, then putting it into the database (either in the data itself or logic in a view).

If that business logic is somewhat dynamic, then using Brian's suggestion of custom columns might be appropriate. This might be a formula fully embedded in the PQ Column, or you may be able to use an Excel table to help define the rules, which is more easily updated.

Cheers

 

[DickyMoo]

We have approx:

- 10,000 Account Codes (type of transaction, eg bank charges, income)
- 1,000 Cost Centres (business area, eg Research, Finance)
- 30 Journal Types (eg bank journal, accruals)
- unlimited Transaction References

When a new transaction is posted it has a combination of these codes, and we have defined certain combinations to relate to certain categories of cashflow, eg Membership Income, Supplier Payments. Ideally, as you mentioned, the cashflow category would be assigned when the transaction hits the database, but I think that is just above my skill set at the moment, so I'm looking more at the extract and define area.

From both of your comments, I think my choices are currently:

- Create a separate calculated column in PQ for each category, then combine these categories into one column
- Extract the whole dataset to PowerPivot and define a measure for each category

I will try each of these and see how they work.

Thank you both for your comments, it has really helped to talk it through.

Richard

 

[jafa1970]

I might be misunderstanding how your categories work, but it seems there should be an approach that does not require a column or measure for each category, that you should be able to put all the logic for the category into one place.

Options for this might be a single column in PQ using IF THEN ELSE, or in PowerPivot as a calculated column (not a measure), using SWITCH. I have applied some complex business logic using multiple fields and even RELATED fields (ie in other tables).

Happy to try to assist if you want to put a mock data set up with the layout of your data, what you want to achieve and the business logic to do so.

Cheers

 

[DickyMoo]

Hi,

I have attached the lookup file we currently use, it shows the ranges of the various code types that make up each cashflow category. For example, transactions are classed as 'Donations & Legacies' if they are posted to:

- an Account Code of either 1102 or 1108
- and, a Journal Type of either CHQ, BS, EPDQ, or PDQ
- and, a Cost Centre in the range AAA98 to XSZ99

It is this multi-level classification that I am struggling with. (Note, I am ignoring the Transaction Reference in this example).

This is why I'm considering doing the classification one group at a time, unless I am missing something?

Thanks

 

[jafa1970]

This might be a possible approach to have one column for Category. It is somewhat complex / confusing but if you can understand AND / OR combinations it might work.

The basic syntax is
= if <true/false test> then "something" else
if <true/false test> then "another thing" else
"no other true results"

For your multi field requirements this will look like this:

=
if List.AllTrue ({List.AllTrue({[ACCOUNT CODE]>="TIF1002" , [ACCOUNT CODE]<="TIFZ012"}) , List.AnyTrue({ List.AllTrue({[JOURNAL TYPE]>="ANN",[JOURNAL TYPE]<="CRR"}), List.AllTrue({[JOURNAL TYPE]>="DRS",[JOURNAL TYPE]<="PPL"}), List.AllTrue({[JOURNAL TYPE]>="SAJ",[JOURNAL TYPE]<="YE"}),  }) }) then "Events and conferencing invoices" else
if List.AllTrue ({ List.AnyTrue({ List.AllTrue({[ACCOUNT CODE]>="TA99" , [ACCOUNT CODE]<="TG99"}), List.AllTrue({[ACCOUNT CODE]>="TIL99" , [ACCOUNT CODE]<="TR99"}), List.AllTrue({[ACCOUNT CODE]>="TR99" , [ACCOUNT CODE]<="TZ99"}) }) , List.AnyTrue({ List.AllTrue({[JOURNAL TYPE]>="ANN",[JOURNAL TYPE]<="CRR"}), List.AllTrue({[JOURNAL TYPE]>="DRS",[JOURNAL TYPE]<="PPL"}), List.AllTrue({[JOURNAL TYPE]>="SAJ",[JOURNAL TYPE]<="YE"}),  }) }) then "Miscellaneous invoices" else
if List.AllTrue ({List.AllTrue({[ACCOUNT CODE]>="TS1321" , [ACCOUNT CODE]<="TS2868"}) , List.AnyTrue({ List.AllTrue({[JOURNAL TYPE]>="ANN",[JOURNAL TYPE]<="CRR"}), List.AllTrue({[JOURNAL TYPE]>="DRS",[JOURNAL TYPE]<="PPL"}), List.AllTrue({[JOURNAL TYPE]>="SAJ",[JOURNAL TYPE]<="YE"}),  }) }) then "Education invoices" else
if List.AllTrue ({List.AnyTrue({[ACCOUNT CODE]="1122" , [ACCOUNT CODE]="11262"}) , List.AllTrue({[JOURNAL TYPE]="BS }) }) then "Investment Income" else
"Not mapped"

Have attached your file with the above as well.

(Note, have not tested that I have the syntax correct, lining up the ({ }) can be a bit trickey)

Cheers

 

[DickyMoo]

Thanks jafa, that's very kind of you.

I'll have a look tonight and get back to you.

Rich

 

[DickyMoo]

Hi,

This works perfectly. I'm slightly worried that it will look too complicated to anyone having to update it in the future, but I suppose that's part of the game.

Without wanting to overstay my welcome on this thread, is it possible to create a function for each category, and call each function? This might look simpler, and be easier to update for changing dimensions. (I'm newish to PQ, just finding my feet).

Thanks