4

Is there anyway in Excel Power Query to duplicate the Applied Steps from a prior query so that all of the applied steps show in the duplicate? As in, I'd like to duplicate a 10 step query to step 6 and then add in new steps from where it forks from the prior query.

It looks like the "Duplicate" option within PowerQuery will duplicate the entire query and not allow any steps to be changed.

Thank you for your help with this.

DrewDaddio
  • 65

2 Answers2

3

In the Power Query project you would like to copy the first 6 of your 10 steps:

Select tab View and option Advanced Editor

Copy everything there and either keep it in your clipboard for immediate use or store it somewhere temporarily like notepad. It will look something like this:

let
      Source = [Bunches of stuff],
      #"Step 1" = [All the things that happen],
      #"Step 2" = [All the things that happen],
      #"Step 3" = [All the things that happen],
      #"Step 4" = [All the things that happen],
      #"Step 5" = [All the things that happen],
      #"Step 6" = [All the things that happen],
      #"Step 7" = [All the things that happen],
      #"Step 8" = [All the things that happen],
      #"Step 9" = [All the things that happen],
      #"Step 10" = [All the things that happen]
in
      #"Step 10"

Open your new project, navigate to Advanced Editor, and replace whatever is in that window.

  1. Remove steps 7-10 of your query, each begins with #"
  2. Delete the comma [,] that appears at the end of your 6th query step (you may need to scroll right to find it if query step 6 is really involved)
  3. Below the line "in" is your 10th query step; replace it with your 6th in the same format #"[Query step description]"

Now it should look like this:

let
      Source = [Bunches of stuff],
      #"Step 1" = [All the things that happen],
      #"Step 2" = [All the things that happen],
      #"Step 3" = [All the things that happen],
      #"Step 4" = [All the things that happen],
      #"Step 5" = [All the things that happen],
      #"Step 6" = [All the things that happen]
in
      #"Step 6"
  1. Verify the bottom of the Advanced Editor says "No syntax errors have been detected" (try again if errors are found)
  2. Click Done

Your Applied Steps window shows everything you typically work with and each step is editable.

Tisconie
  • 97
0

Thanks a lot , i have a 1 question. iam working on a 1 pdf file to import from Power Query Editor (PQE).

  1. page001 has a unique steps which i have applied
  2. after page 001, meaning 002 to page 300, i have unique steps
  3. how am i going to apply the below code with the steps to page 002 to page 300?
  4. if there is such a code which can bulk edit, after selecting which page can i paste the code?

let Source = Pdf.Tables(File.Contents("C:\Users\desktop\Downloads\sample.pdf"), [Implementation="1.3"]), Page1 = Source{[Id="Page002"]}[Data], #"Changed Type" = Table.TransformColumnTypes(Page1,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}), #"Removed Top Rows" = Table.Skip(#"Changed Type",2), #"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Top Rows",3) in #"Removed Bottom Rows"

David Glamour
  • 1