Forum Discussion

DNMakinson's avatar
DNMakinson
Copper Contributor
Feb 03, 2025

Power Query Simplification

I have a series of power queries that are almost identical. Each has an input table from an Excel Sheet, and and output that is a new Sheet in the same workbook.

 

Query one gets data from Named Range _01, and outputs transposed data to table _01_1 on a newly created Sheet 01.

The Query two gets data from Named Range _02, and outputs transposed data to table _02_1 on a newly created Sheet 02.

The queries are identical except for the input and output.

So, I have like 30 queries doing almost the same thing. 

If I want to do something new to the data, I have to insert new Mcode 30 times.

Is that a way to use an input array of Range Named Ranges, and then potentially output names, and call a single query multiple times, using the array as variable inputs in the query, so that I can have single query or section of the query where I can make a change to the code only one time?

BI & Data Analysis
excel
Excel on Mac

3 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 04, 2025

    Power Query can't create new sheet, and we can't say to Power Query by parameter at which place of the workbook to load it. This part of the job is more is for scripting tools or VBA.

    If with Power Query we may manually create 30 new sheets, write core query/function which transforms source range into output using source name name as parameter. When write 30 queries with text like

    let Source = core(name) in Source

    and load each of them into proper place.

  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Feb 04, 2025

    You may consider on following:

    • Create Parameters for Named Ranges and Output Names:
      • Go to Power Query Editor.
      • Create two parameters: one for the input range name and another for the output table name.
    • Modify Your Query to Use Parameters:
      • Use the parameters in your query instead of hardcoding the range and output names.

    Example for reference:

    1. Create two parameters:

      • InputRangeName of type Text.
      • OutputTableName of type Text.

    2. Use the parameters in your query:

    let
    InputRange = Excel.CurrentWorkbook(){[Name=InputRangeName]}[Content],
    TransposedData = Table.Transpose(InputRange),
    OutputTable = Excel.CurrentWorkbook(){[Name=OutputTableName]}[Content],
    FinalTable = Table.Combine({OutputTable, TransposedData})
in
    FinalTable

    3. Call the Query Multiple Times:

    • Create a new query that generates a list of range names and output table names.
    • Use a custom function to call the main query for each pair of input and output names.

     

    Example for custom function:

    1. Create a new blank query and name it InvokeQuery.
    2. Use the following code:
    let
    Source = Table.FromRecords({
        [InputRangeName="_01", OutputTableName="_01_1"],
        [InputRangeName="_02", OutputTableName="_02_1"],
        // Add more records here...
        [InputRangeName="_30", OutputTableName="_30_1"]
    }),
    Result = Table.AddColumn(Source, "Output", each MainQuery([InputRangeName], [OutputTableName]))
in
    Result

     

    • Source is a table that holds pairs of input and output names.
    • MainQuery is the name of the query that uses parameters.

     

    • DNMakinson's avatar
      DNMakinson
      Copper Contributor
      Feb 04, 2025

      Thank you for your response.

      Not sure I fully understand how to integrate those into my existing query.. My query apparently uses some implicit actions (like the output creates tabs (sheets) in the workbook. So, the source is not explicitly stated as Source. I have copied my entire (single) query, so you can show me how to add the parameters in. Also, does it matter what order the parameters, the Invoke Query, and the main query reside?

      I did create the (2) parameters as you suggested.

      Query for 1st set of data, that I wish to repeat for each. The #"01B is a tab that is created. It is also the name of the Query.

      let

      #"01B" = Excel.CurrentWorkbook(){[Name = "_01B"]}[Content],

      #"Promoted headers" = Table.PromoteHeaders(#"01B", [PromoteAllScalars = true]),

      #"Filtered rows" = Table.SelectRows(#"Promoted headers", each [Name] <> null and [Name] <> "Name"),

      #"Reordered columns" = Table.ReorderColumns(#"Filtered rows", {"Num", "Gym", "Name", "Lvl", "Age Div", "Flight", "Squad", "Events"}),

      #"Removed columns" = Table.RemoveColumns(#"Reordered columns", {"Column3", "Column10"}),

      #"Renamed columns" = Table.RenameColumns(#"Removed columns", {{"Column5", "Checked In"},{"Lvl", "Level"},{"Squad", "Starts"}}),

      #"Replaced value" = Table.ReplaceValue(#"Renamed columns",

      each [Starts],

      each if [Events] = "Women" and [Starts] = "A" then "Vault"

      else if [Events] = "Women" and [Starts] = "B" then "Bars"

      else if [Events] = "Women" and [Starts] = "C" then "Beam"

      else if [Events] = "Women" and [Starts] = "D" then "Floor"

      else if [Events] = "Men" and [Starts] = "A" then "Floor"

      else if [Events] = "Men" and [Starts] = "B" then "Pommel"

      else if [Events] = "Men" and [Starts] = "C" then "Rings"

      else if [Events] = "Men" and [Starts] = "D" then "Vault"

      else if [Events] = "Men" and [Starts] = "E" then "P-Bars"

      else if [Events] = "Men" and [Starts] = "F" then "High Bars" else "",

      Replacer.ReplaceValue, {"Starts"}),

      #"Removed columns 1" = Table.RemoveColumns(#"Replaced value", {"Events"}),

      #"Sorted rows" = Table.Sort(#"Removed columns 1", {{"Gym", Order.Ascending}})

      in

      #"Sorted rows"

       

Resources