4

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.

bpeikes
  • 3,495
  • 9
  • 42
  • 80
  • I would suggest to keep your data (data file) and the PowerQuery data transformations (PQ file) in separate documents. Then you can easily copy your PQ file to use it with different data. If you create a folder for each data file and put your PQ file in these as well, you can fully automize the process - as long as the data structure stays the same. – Qualia Communications Aug 12 '21 at 20:23
  • How do you export the pq file? That sounds like the way to go. Thanks. – bpeikes Aug 12 '21 at 23:26
  • Please find my answer below. – Qualia Communications Aug 15 '21 at 15:46

2 Answers2

4

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.
Qualia Communications
  • 715
  • 6
  • 11
  • I did something like option 2. I added a sheet called Configuration. Added a cell with the formula you specified, and then named the cell. Then I was able to reference the path when building the Source. This was necessary because PowerQuery does not support relative paths. – bpeikes Aug 15 '21 at 16:15
1

Copy and Paste

In PowerQuery right-click on the final query and select copy. Open a new Excel workbook, open PowerQuery and paste the query into the queries pane. All dependent queries and parameters will be copied as well. Then you can adjust the query steps and save the new workbook.

Peter
  • 10,959
  • 2
  • 30
  • 47
  • What if I want to apply the same Transformation steps to different input files and have the output for each file displayed as a separate table in the same Excel file? Creating a copy of the same Transformation power query for each input file seems like an overkill. (and becomes difficult to manage when the number of input files grows) Thank you – Confounded Apr 04 '23 at 02:30