Forum Discussion

Maxitco's avatar
Maxitco
Helper I
3 years ago

Excel - Power Query - Bank Statements - Combining Data based on Date Values, Type, Descriptions

Hello All, 

 

I am attempting to combine all my historic bank statements together by using Power Query.  I have already selected the source folder to bring 2 months of PDF bank statements to try out this method and have combined them together 

 

The following issues that I am having are as follows:

 

1. I can't seem to combine all the transaction rows which all full under the specific date. So the first line shows a date and any other transactions that show for the same period show as 'null'.   So the 'null' data in the 'Dates' column should match the first date for any new transaction detail for that date.

2. The bank statement has a 'Transaction type' column which is split between 'null', '(((' & 'DD', and this is shown on the first specific rows that have a new transaction associated with it. So if there is a 'null' value below a transaction type of 'DD' then this transaction description is linked with the first row.

3. The bank statement has a 'Payment type and details' column which lists the transaction information, however there are multiple transaction rows that are made under the same date and are linked to a specified 'Transaction Type' for each and I want to combine these togeter somehow and the additional information to bring together (prefer onto one line for each transaction type).

4. The 'Balance' column I don't really mind as much as I wish to them transpose the data into a Pivot table, but what you can see is that there is a numerical value that has a 'D' in it. 

5. I wish to remove all 'Balance Brought Forward, and Balance Carried Forward' rows and Columns as only interested in the 'Paid out' and 'Paid in' amounts. 

 

Power query is new to me, however I have spent a few hours learning what I can understand from videos, but just can't seem to implement to clean up this data.

 

I hope someone can be of assistance and help with the above. 

 

Looking forward to what options I can take to resolve this. 

 

Many thanks in advance.

General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

33 Replies

  • Poohkrd's avatar
    Poohkrd
    Advocate I
    3 years ago

    Hi, Maxitco, can you show example of your data in xlsx/pbix file, and the result you want.

    • Maxitco's avatar
      Maxitco
      Helper I
      3 years ago

      Hi Poohkrd 

       

      Thanks for reply. 

       

      I am new to this forum and can't seem to upload the xlsx spreadsheet, thou I have uploaded an additional picture which is a snip of what I have prepared in a new sheet to assist you. 

       

      I hope you can understand

      • jbwtp's avatar
        jbwtp
        Memorable Member
        3 years ago

        Hi Maxitco,

         

        Please try this:

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3NNQ3MlLSUQIiJ0cfRz9nVwgHiAwNDJRidaKVTOCKwjyDgaSHq6NPiAdcGVgNhBns4R8A04skA9GGkETV5BtTamBgZBYM4RnBNVqgWevpF+KjYGCKzQjnIFfHEM8wVwVnH2zSpqZQHxqZwiQMgD43wulzE1Ol2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Type = _t, Details = _t, In = _t, Out = _t, Balance = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Type", type text}, {"Details", type text}, {"In", type number}, {"Out", Int64.Type}, {"Balance", Int64.Type}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Date"}),
    #"Replaced Value" = Table.ReplaceValue(#"Filled Down",null,0,Replacer.ReplaceValue,{"In", "Out", "Balance"}),
    Custom1 = List.Accumulate(Table.ToRecords(#"Replaced Value"), {}, (a, n)=> 
            if n[Details] = "BALANCE" then {n} & a else 
            if n[Type] <> "" and n[Type] <> null then {n} & a else 
                let 
                rLast = List.First(a),
                transform = Record.TransformFields(rLast, {{"Details", each _ & " " & n[Details]}, {"In", each _ + n[In]}, {"Out", each _ + n[Out]}}),
                out =  {transform} & List.Skip(a)  //List.Skip(a) & {transform}
                in out ),
    Custom2 = Table.FromRecords(List.Reverse(Custom1), Value.Type(#"Changed Type"))
in
    Custom2