I have 50 csv files each with data for a state. Need to perform the same data preparation then generate 50 excel files with a query for each state. I know how to input multiple files from the same folder. My question is: is there a way to general multiple files using power query?
Thank you in advance!
Hi Lydia,
No, Power Query can only output data to a single file i.e. the file containing the query. You'd need to create 50 separate query files in order to generate 50 separate Excel files.
Assuming the structure of the CSV files is the same, you could create one Excel file for the first CSV, then copy that file and edit the query to point at the second CSV file and so on for all 50.
Mynda
I know after you collect all your data and create your proper dataset, you can create a pivot table and make it create 50 separate sheets automatically. Not sure about different workbooks.
Hi Lydia,
If you end up with 50 sheets you can save each one as a workbook using the attached VBA.
Sub SaveSheetsAsWorkbooks()
Dim ws As Worksheet
Dim wb As Workbook
Dim Path As String
' Change this to where you want to save the files
Path = "d:temp"
Application.DisplayAlerts = False
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
Set wb = Workbooks.Add
wb.Sheets(1).Name = "xx_OLD_xx"
ws.Copy Before:=wb.Sheets(1)
wb.Sheets("xx_OLD_xx").Delete
wb.SaveAs Path & ws.Name & ".xlsx", xlOpenXMLWorkbook
wb.Close
Set wb = Nothing
Next ws
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Regards
Phil
