Forum Discussion
Excel - Power Query - Bank Statements - Combining Data based on Date Values, Type, Descriptions
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.
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
Hello jbwtp
Thanks for your reply.
I am really sorry to say that I don't follow your latest instructions in respect to the advance code you supplied. My terminology and language skill in Power Query is still at the beggining level and any adjustments that I have made have not been directly through the advance coding, only the 'Applied Steps'.
Can you kindly provide highlights with the areas that need to be updated within the code in question which need to be referenced, moved or changed according to your direction?
Are you able to use the 'Snipping tool' when posting to highlight specific areas where the information is and where it needs to go and reference the text lines that needs updating at all, deleting or moving up or down the code?
I know that, as highlighted below in yellow, that the column names can be changed and would be reflected in the code if updated in the Query Settings code or can be amended in the code directly.
Just as an example, as per your first point I get a error message when updating the #"Filled Down" line code to #"Changed Type"
Of course I would need to add the source of the data as below to the code to pull from the folder.
let
Source = Folder.Files("C:\Users\Documents\HSBC Excel Power Query - Accounts Download\HSBC Advance Account"),Thanking you for your patience and advice in advance.
Kind regards,
Max
Hi Max,
P.S. Sorry I realised that this is my code on the screenshot above. Re-wrting.
Please share the code that brought you to the step on the first screenshot in your original post.
I will intergate the remaining bits.
Cheers,
John
Hi John,
No problem at all.
Please see below the screenshot along with the code, and this is the stage that I managed to get to at the start.
Date
Transaction Type
Payment Type and details
Paid Out
Paid In
Balance
let
Source = Folder.Files("C:\Users\HSBC Excel Power Query - Accounts Download\HSBC Advance Account"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Date", type date}, {"Column2", type text}, {"Payment type and details", type text}, {"Paid out", type number}, {"Paid in", type number}, {"Balance", type text}}),
#"Removed Source Name Column" = Table.RemoveColumns(#"Changed Type",{"Source.Name"}),
#"Renamed To Transaction Type Column" = Table.RenameColumns(#"Removed Source Name Column",{{"Column2", "Transaction Type"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed To Transaction Type Column",{{"Date", type date}}),
#"Replaced ((( with VIS Value" = Table.ReplaceValue(#"Changed Type1",")))","VIS",Replacer.ReplaceText,{"Transaction Type"})
in
#"Replaced ((( with VIS Value"I hope the above is ok.
Thanks
Max