We have 4GB csv file which is the source for power query in an Excel document. It takes some time to set up all of the transformations, and we would like to be able to reuse the steps when creating other documents which need to import into the data model files of the same format.

Is there a way to save the query and reuse it in another document? I've seen some references to copying the query text from the Advanced Editor, but it seems like there should be a better way of doing it.

Separation of data and PowerQuery transformations

I assume, you opened your Excel data file and did all PowerQuery transformations within it. In order to separate them, you could either go for Peter's solution or you make two copies of that file, one for the data (e.g. "data.xlsx") and the other for the transformations (e.g. "PQ_transformations.xlsx"). Either way, you will have to do some adjustments.

Adjustments

• Remove all PQ queries from the data file.
• Alter the PQ file. It depends on whether you would like to change the location of each data file within PowerQuery (Option 1) or not (Option 2).

Option 1: Select the data file within PowerQuery.

• Open the PQ editor
• Go to the first query of your transformations and replace the first statement (which should look like = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]) by = Excel.Workbook(File.Contents("[PATH]\data.xlsx"), null, true) with [PATH] being a placeholder for your file's location.
• Close the PQ editor
• Delete the tab that contained your original data.

Option 2: Apply transformations without editing PowerQuery

The following setup assumes that you organize your data files in different folders. You can then copy your PQ file into each of these folders, open it and click on "Data"/"Update all" to apply your transformations to the data file in the given folder.

Notes:

• I assume that all data files have the same structure and name.

• I define the folder in Excel and not in PowerQuery to allow users that have no knowledge of PQ to manually change the folder by overwriting the formula in case they do not want to copy the file all the time.

• Add a tab called "Paths".

• Select A1 and enter Current folder.

• Select A2 and enter =MID(CELL("filename"),1,FIND("[",CELL("filename"))-1). This formula provides you with the folder of the current file as soon as it has been saved.

• Select range A1:A2 and bring it into PQ editor by selecting the "Data" ribbon and choosing "From table/area" out of the "Request and transform data" section.

• A new query is generated, showing you the current folder.

• Open the "Advanced Editor" ("Start"/"Advanced Editor"), change the name of the second step to "SetTypes" and add the additional lines. The result should look similiar to this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    SetTypes = Table.TransformColumnTypes(Source ,{{"Current folder", type text}}),
    GetPathAsValue = SetTypes{0}[Current folder],
    ShowFilesInPath = Folder.Files(GetPathAsValue),
    FilterForDataFile = Table.SelectRows(ShowFilesInPath, each ([Name] = "Data.xlsx"))
in
    FilterForDataFile 

• Close the "Advanced editor" and accept the changes.
• You should see a row which features your data file.
• Click on "Binary" in the "Content" column to see a list of all tables and sheets in that file.
• Select the desired "Sheet" or "table" whatever you usually have in your data file.
• Rename the query to "GetFile"
• Go to the first query of your original transformations and replace the first statement (which should look like = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]) by = GetFile.