Combine Files in Bulk Using Power Query (Best Practices)
1. Select Folder in Power Query and Click Transform Data
*Put files to merge in one folder if possible
2. Remove Other Columns with [[Name], [Content]]
*Use this step to filter out any unneeded files
*Square brackets [ ] are used as the column access operator
*Can also use “Remove Other Columns” but that adds an extra step
3. Duplicate Name Column and Parse Name Data as Needed
*Use this step to extract any needed info from file name
4. Add Index and Mod Columns
*This step allows splitting the data into smaller sets to prevent maxing out the computer's memory
*For small sets of data, this is not needed
*For large, numerous files, change MOD to 10 or more to ensure all data will load without exceeding memory limitations
5. Reference Query and Rename Data0
*We will have one query for Files and several for the Data
*By referencing the Files query, we can change the filtering or other file name operations without affecting any subsequent steps
6. Remove Other Columns with [[Name], [Content], [MOD]]
*This is optional to remove extra columns
*The File query can be used later to add back needed data
7. Filter 0’s in Mod Column
*Filtering by MOD number is so we take only 1/5th of the data (per Query)
8. Process 1 File Manually
*Steps performed here will be used to create the custom function that will be used on all files in all data queries
9. Duplicate Data0 Query and Convert to Function Using each _
*Delete steps above #Imported CSV” and replace Content with _
*Add each above let
10. Remove Unneeded Steps and Use Function Instead
11. Modify Formulas to Use Source Value as Filter Value
*This step is so we only need to put the filter value in one place and the formulas will update appropriately.
12. Duplicate Data Query for each MOD and Rename
*Change source in each copied Data0 query to the next MOD number
*Rename each Query to match MOD number
13. Combine Loaded Data in New File using Power Query
*Pick the Data0 table, then delete steps to select all Data tables
*Change first null to true to promote headers
*Filter for tables and Data names
*Use [[Data]] to keep only tables
*Expand