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.