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 ou...
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
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
    • Maxitco's avatar
      Maxitco
      Helper I
      3 years ago

      jbwtp 

       

      Wow, thank you very much, much appreicated. 

       

      The 'Source' however has changed and does not look for the given folder that has the bank statements to pull the data from. 

       

      So how do I update the source to seek the folder statements or amend the code that you have provided to each time I add a new statement I can click on update and pull in the new data?

       

      Also the column titles will be listed as below, so I am unsure how this would also change the code you kindly provided.

       

      Date

      Transaction Type

      Payment Type and details

      Paid Out

      Paid In

      Balance

       

       

      The below is a snip of the basic folder location and PDF bank statement to pull the data from.

       

      C:\Users\HSBC Advance Account

       

      So normally I would go into the 'Source' Applied Steps and edit the location to where the PDFs are stored, but this amendment only shows the table?

       

       

       

      Hope there is a way to do this.

      • jbwtp's avatar
        jbwtp
        Memorable Member
        3 years ago

        Hi Maxitco,

         

        1. How to incorporate the code into your existing codebase:

        a. As a separate query: Change reference to #"Changed Type" in the line below to reference to the name of your existing query:

        #"Filled Down" = Table.FillDown(#"Changed Type",{"Date"}),

         b. Add to your existing query: copy the query from the line above to the rest of the query and then paste into your query after the [so far] last step (do not forget to remove the existing "in" staement at the end of your existing query and add comma at the current last line in your query [this is syntax requirements]).

         

        2. Change of column names:

         rename references to columns in my code: Type to Transaciton Type, Details to Payment Type and details and so on.

         

        Hope this helps,

        Kind regarfds,

        John