Our task is to make consolidation procedure of multiple files from specific directory. Note that at this example we use MS Excel (product Microsoft 365).
This aggregation task could be divided into subtasks:
Subtask #1 Source creation: tab Data->Get Data->From Folder:
To modify query we have to click Transform Data button:
Remain only column [Name] and column [Folder Path]:
We should filter list of files. It is required to remain only that type of file that we will read.
We need to set new column with full path to files:
Subtask #2 file structure analysis. We have to build file mask analysis: Get Data->From File->From Text/CSV (you can use your target format of the files)
We navigate to Power Query Editor when click Transform Data button. In Editor mode we should select two columns (desc_ - description of item and Inc - item volume in USD Dollars):
After we should navigate to Advanced Editor and transform query into function with one parameter:
We have to re-write query:
If we do this successfully we will navigate to Editor and see new function with parameter:
Rename function Annual_Budget_2010_Table_E_FCC into get_csv_file_data_func in Quey Settings:
In query tutorial_aggregation we have to delete not needed column (Folder Path). Then add custome column:
Rename column mane Custom into csv_files_data:
Select function get_csv_file_data_func and pass parameter (Full_Path column values ) into it:
Result query will be load into Excel work sheet:
Our task of csv files aggregation is completed. See new examples for Power BI and Power Query at https://virtualanalytics.azurewebsites.net/ Thank you!