Power query assistance

%3CLINGO-SUB%20id%3D%22lingo-sub-1363112%22%20slang%3D%22en-US%22%3EPower%20query%20assistance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1363112%22%20slang%3D%22en-US%22%3EHi%20guys%2C%3CBR%20%2F%3ENew%20to%20the%20community!%20And%20very%20new%20to%20power%20query%20I%20have%20a%20task%20where%20I%20have%20to%20import%20data%20containing%20a%20company%E2%80%99s%20bank%20statements%20and%20be%20able%20to%20categorise%20it%E2%80%99s%20cash%20inflows%20and%20outflows%20based%20on%20if%20text%20contains%20a%20certain%20criteria..%20Unfortunately%20there%20are%20probably%2070%20categories%20or%20so..%20I%20am%20having%20some%20success%20with%20the%20conditional%20column%20at%20the%20moment%20but%20I%20feel%20that%20the%20conditional%20column%20is%20limited%20as%20I%20can%E2%80%99t%20find%20a%20way%20to%20incorporate%20Nested%20if%20(%20and%20statements%20to%20categorise%20my%20cash%20flows..%3CBR%20%2F%3E%3CBR%20%2F%3EA%20specific%20example%20of%20a%20problem%20I%20have%20%3A%3CBR%20%2F%3EIf%20a%20bank%20statement%20contains%20the%20word%20%E2%80%9Ccharges%E2%80%9D%20-%20then%20categorise%20as%20charges%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20a%20bank%20statement%20contains%20the%20word%20%E2%80%9Cpurchase%20ledger%E2%80%9D%20-%20then%20categorise%20as%20purchase%20ledger..%3CBR%20%2F%3E%3CBR%20%2F%3EA%20problem%20I%20face%20is%20when%20the%20conditional%20column%20has%20limits%20when%20the%20bank%20statement%20contains%20both%20%E2%80%9Ccharges%E2%80%9D%20and%20%E2%80%9Cpurchase%20ledger%E2%80%9D%20in%20its%20name.%3CBR%20%2F%3E%3CBR%20%2F%3EAnother%20problem%20I%20have%20is%20when%20I%20input%20the%20following%20query%3A%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20text%20contains%20%E2%80%9Cadv%E2%80%9D%2C%20output%20%3A%20interest%20payments..%3CBR%20%2F%3EThis%20is%20a%20real%20issue%20as%20it%20miscategorises%20bank%20statements%20with%20advance%20in%20its%20description%20as%20interest%20payments%20also%20%3CLI-EMOJI%20id%3D%22lia_confused-face%22%20title%3D%22%3Aconfused_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3E%3CBR%20%2F%3EDo%20any%20of%20you%20guys%20know%20an%20easy%20way%20to%20sort%20out%20my%20problems%20using%20power%20query%20ie%3A%3CBR%20%2F%3E(multiple%20categories%20and%20at%20times%20multiple%20criteria%20needed%20to%20categorise%20cash%20flows)%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20anyone%20wants%20any%20further%20info%20and%20files%20for%20more%20clarification%20would%20be%20happy%20to%20provide%20it.%3CBR%20%2F%3E%3CBR%20%2F%3EHope%20you%20guys%20can%20help!%20Many%20thanks%20for%20your%20assistance%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1363112%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1363244%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20query%20assistance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1363244%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F657670%22%20target%3D%22_blank%22%3E%40Michaellaudrup9%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20looks%20like%20that%20not%20an%20issue%20with%20Power%20Query.%20Formal%20logic%20is%20not%20defined%20correctly.%20For%20the%20first%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eif%20condition1%20then%20option1%3C%2FP%3E%0A%3CP%3Eelse%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%20%26nbsp%3B%20if%20condition2%20then%20option2%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%20%26nbsp%3B%20else%20NOTHING%20IS%20DEFINED%20FOR%20THAT%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPower%20query%20is%20only%20maps%20your%20logic%2C%20it%20doesn't%20create%20its%20own.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Frequent Visitor
Hi guys,
New to the community! And very new to power query I have a task where I have to import data containing a company’s bank statements and be able to categorise it’s cash inflows and outflows based on if text contains a certain criteria.. Unfortunately there are probably 70 categories or so.. I am having some success with the conditional column at the moment but I feel that the conditional column is limited as I can’t find a way to incorporate Nested if ( and statements to categorise my cash flows..

A specific example of a problem I have :
If a bank statement contains the word “charges” - then categorise as charges

If a bank statement contains the word “purchase ledger” - then categorise as purchase ledger..

A problem I face is when the conditional column has limits when the bank statement contains both “charges” and “purchase ledger” in its name.

Another problem I have is when I input the following query:

If text contains “adv”, output : interest payments..
This is a real issue as it miscategorises bank statements with advance in its description as interest payments also

Do any of you guys know an easy way to sort out my problems using power query ie:
(multiple categories and at times multiple criteria needed to categorise cash flows)

If anyone wants any further info and files for more clarification would be happy to provide it.

Hope you guys can help! Many thanks for your assistance
1 Reply
Highlighted

@Michaellaudrup9 

It looks like that not an issue with Power Query. Formal logic is not defined correctly. For the first

 

if condition1 then option1

else

    if condition2 then option2

    else NOTHING IS DEFINED FOR THAT

 

Power query is only maps your logic, it doesn't create its own.