Power query using find and replace feature

[Waverly]

Attached are two CSV files representing March & April 2022 bank statements and I used power query to combine and load them into the attached spreadsheet table and I experienced one little problem and will be experiencing something else for the future.

It takes three months’ of bank statements to do the quarterly sales tax returns, but they May 2022 bank statement won't be available until later on this week; so the problem that I’m going to have in the future is bringing in that month’s bank statement into this data set (the attached spreadsheet).

The bank statement transactions for sales are reflected entirely through something called “Zelle” payments, and I wanted to do away with the beginning descriptions for those Zelle transactions, and leave only the customer’s names.

However during the power query process, I used the find and replace feature and told the program to search for “Zelle Transfer Conf# *;” and replace it with nothing, and it did nothing. I’ve brought the text into Excel as a table and then ran the find and replace feature, using the same description, and in Excel, it is successfully found and replaced the 107 or so transactions, leaving only the customer’s names as shown in the attached spreadsheet.

My question is why did the find and replace query not work in power query?

And then, my last question is how do I bring the May 2022 bank transactions (from a CSV file) into the attached spreadsheet? Will power query still work? Or, do I have to massage the data in a separate table then copy and paste it into the attached spreadsheet? Or will the power queries steps that I took to load and transform the attached spreadsheet still be there, and can they be used for adding new data?

[alansidman]

Cannot review and test as I get the following message as the source file in question is not available.

An error occurred in the ‘Sample File’ query. DataSource.NotFound: File or Folder: We couldn't find the folder 'X:\Clients' Documents\Belle Hair Suite Inc\CSV\*'.
Details:
X:\Clients' Documents\Belle Hair Suite Inc\CSV

However, when I bring one of the CSV files into PQ and do a replace, it works without issue.

[Waverly]

Baffling, as on my end where the data files (CSV files) were in the right location, and they find and replace function did not work.

Does this hint that if the spreadsheet are CSV files are moved to a different location that the power query structure (design) will not work?

And, what about the possibility of using or "reusing" when wanting to add additional CSV files? When I get the May 2022 bank data later this week will the power query structure still be there to reuse? Or, well I have to re-create the entire procedure all over again?

For the attached spreadsheet I attempted to do two things, only two of which were successful:

1. remove the file name column
   duplicates the payee column
   find replace the phrase “Zelle Transfer Conf# *;” (which was unsuccessful)

[AliGW]

Are the CSV fies in the same folder as the Excel file containing the query? If so, move them to a sub-folder.

[Waverly]

Power query is indeed different and more challenging than the typical spreadsheet, and I see that I have quite a ways to learn about it.

But to answer everybody's question: "yes, the CSV files are in the same location."

And, if I move those two CSV files to another location such as when I attached him to my original post, does that defeat the power query structure? In that whatever parameters I set up when transforming the data are no longer there to be seen?

I'm just trying to perfect this technique, as this has to be done repeatedly throughout the year, presumably four times a year for the US quarterly sales tax returns.

So, I've got a take a new three file set of CSV files which contain the banking information load them into power query and then transform them into a data table.

Now, I've noticed the bank format doesn't change over the course of the year, so I'm thinking and hoping that whatever power query steps I created in the initial file that I can use them again and again for the remainder quarters without having to reinvent the wheel.

I was hoping to take the file that is created right now, rename it and use it for the future sales tax period. E.g., take the February 2022 sales tax spreadsheet rename it to May 2022,delete all records therein, and then append from the new three CSV files containing the March-May bank data.

I thought this might be a "template" concept, as accountants do repeat work over and over again (but with different numbers, are different data set). I'm repeating the same power query structure and design every three months but with a new set of CSV files.

I mentioned that I still have May 2022's bank data to import when it is available later this week. I didn't want to re-create the power query structure, repeating those three steps manually each and every time (removing a column, copying a column and then finally find replace).

I somehow wanted that once I have this power query structure designed, that I can use it for future work.

[AliGW]

1. The .csv files (source files) should not be in the same folder as the Excel file containing the query that uses them.
2. If you move source files, then yes, you will have to tell PQ that you have done so - it won't know automatically that you have done so.
3. If new source files have different names to old source files, then yes, you will have to tell PQ, as it will not detect the change automatically.

I think you will need to familiarise yourself with parameter queries. These links should help:

https://goodly.co.in/dynamic-file-path-power-query/
https://exceloffthegrid.com/power-qu...ce-cell-value/
https://docs.microsoft.com/en-us/pow...ery-parameters

Cells containing data for parameter queries can be populated based on the current date, remember, so automating your workflow is entirely possible.

[Waverly]

Final question on this power query issue: what should I do for future periods?

Being that I have to take three months of bank transactions to do a quarterly sales tax return, do I use a current spreadsheet file, with the power query structure therein, and clear out the filters and delete all the transactions (records) and then import the new three months’ CSV files, and then rename or save it to a different filename?

Or, something different?

[alansidman]

You have a couple of options
1. As you have described in post 7
2. In a new file, copy and paste the Mcode
3. Append your new data to the existing workbook and filter out the unwanted information in the PQE or build a parameter to filter the data

Note: If you use option 3 then you will always have the historical data and you can retrieve it as needed.

[Waverly]

How is this solution:

https://www.youtube.com/watch?v=cW1Zw6MlVq8

[alansidman]

The real question is: Have you tried it and does it work for you?

[Waverly]

No, I haven't, but was wondering if it is a good solution to copying the power query structure to a new spreadsheet for the next sales tax period.

[alansidman]

Appears to be ok, but until you test it, how will you truly know.