Forum Discussion
Copying several files including power query into a new folder
Hello,
I was looking for help about how to copy a folder with 3 excel workbooks that hold the data in 3 corresponding sheets, 1 Power Query workbook, and 1 workbook that has a pivot table and chart in one sheet.
The power query combines and has several steps for the data in the 3 workbooks. Then, pivot table and chart workbook references the power query workbook.
When I copy over these files over to a new folder to use for new data, I'd like the references and connections to continue to work. I imagine the sources will need to be updated in the power query but I'm trying to figure out if there is an easy way to update this quickly without having to update every source in the power query, as there are quite a few.
I'm going to keep the file names the same after copying them into the new folder, and I think using a parameter should work. Previously, I was able to change the first step for the source to a folder based on a parameter, but I wasn't sure the m-code would be for the next step when I specify the workbook. I think I already have the m-code for the step that would navigate to the specific table in the workbook.
Otherwise, I currently have just 1 workbook with the 3 data tables in different sheets, pivot table in another sheet, and power query in another sheet. I was thinking it would be better to separate out this big workbook out into several workbooks, so they would run faster, and it'd be easier for coworkers to add data to one workbook, while I add data to another workbook. I think this would be worth it.
Let me know if you have any feedback or suggestions, thanks!
7 Replies
"and power query in another sheet". Do you really need to land result of Power Query into the grid, why don't use data model to build PivotTable?
- Great point. I haven't made and used a full data model yet, although I've tried them out.
The reason for the power query, is for 2 of the data files, once I pull them into power query, I perform column unpivots and grouping. I'm not fully familiar with data models, would I be able manipulate the data the same way with only a data model?cpartridge , yes, Power Query is for data transforming. As soon as you did that you may load result into data model, it's not necessary to have this intermediate result in the grid. And from data model you may generate PivotTable adding DAX measures as necessary.
Hi cpartridge
I was thinking it would be better to separate out this big workbook out into several workbooks, so they would run faster
This won't run faster but slower. In a nutshell: getting data from 3 tables within the same workbook is faster than getting Tablex from Wbook1, Tablex from Wbook2 and Tablex from Wbook3
- Great! This is a key question I had. So, I think I may keep everything as one work book then.
My knowledge is limited in PQ, but as far as I could understand the problem...
When copying several Excel workbooks that are connected through Power Query and Pivot Tables to a new folder, it is important to update the data source references in Power Query to ensure that the connections continue to work. One way to do this is by using a parameter in Power Query to define the folder path where the source files are located. This way, when you copy the files to a new folder, you can simply update the value of the parameter to reflect the new folder path, and all the data source references in Power Query will be updated automatically.
To set up a parameter for the folder path in Power Query, you can go to the Power Query Editor and select “Manage Parameters” from the “Home” tab. In the “Manage Parameters” window, you can create a new parameter and set its “Name”, “Type” (e.g., Text), and “Current Value” (e.g., the current folder path where your source files are located). Once you have created the parameter, you can use it in your data source queries by referencing it using the “@” symbol followed by the parameter name (e.g., @FolderPath).
For example, if your data source query currently looks like this:
let Source = Excel.Workbook(File.Contents("C:\OldFolder\SourceFile.xlsx"), null, true), ... in ...You can update it to use the @FolderPath parameter like this:
let Source = Excel.Workbook(File.Contents(@FolderPath & "\SourceFile.xlsx"), null, true), ... in ...This way, when you copy your files to a new folder and update the value of the @FolderPath parameter to reflect the new folder path, all your data source queries will automatically use the new folder path.
I hope this helps!
- Great!! This is helpful to know the code I would use to specify the specific workbook using a parameter. Previously, I seemed only be able to specify the folder directory, and wasn't sure what code to use to specify the exact file.
