Forum Discussion
Power Query Simplification
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:
- Create a new blank query and name it InvokeQuery.
- 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.
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"
