Apply queries used on one file to all files in the source

[Anonymous]

I'm getting data from multiple folders which each contain multiple CSV files. All of the CSV file's reports are identical in format, some just have different amounts of rows of data. However, all of them need to be identically transformed with the same applied queries steps before using them to build visualizations, as there is some extra text that needs to be removed and then the first row needs to be turned into the column headers. 

However, I do not know how to transform all of the separate files with the same exact steps. I've only been able to do the steps to a sample file, but I do not know how to use those same query steps for each individual file. I'd like to also have it automated as over time there will be more CSV files that will populate the folders, and I'd like to just be able to hit 'refresh' and everything updates. 

The end goal is to then combine all of these reports together with the now identical columns and to combine them into a single dataset to use to build reports and dashboards. 

Thank you in advance and please give any follow up questions

[foodd]

Hello @Anonymous , and thank you for adding your question to the Forum.

Stephanie Bruno's blog post from 2020 is a classic walk-through of two patterns for appending CSV files, the first is functional though ends up looking very messy and not as flexible.  The second is what I believe you are interested in.  This adds a new column to your output with the CSV content (you can also do this for Excel files by using Excel.Workbook instead of Csv.Document).

Good luck!

My favorite clean and simple way to combine csv files in Power BI

[Anonymous]

That article is about 70% of what I need to do, so thank you. The last 30% is that I have these two headers on each report in which I need to extract the date from. This is where I was finding difficulty as well, as I need to add a column which contains that date for every report, and the transformation is a bit complex. Any advice on this piece?

[vicky_]

Have a read of the docs on the folder connector - you might need to change the folder structure, but it will be much quicker than transforming each file one by one.

https://learn.microsoft.com/en-us/power-query/connectors/folder