Forum Discussion
Need help to optimize appending tables created from config table in Power Query
Hi!
I'm building a model for which I need to fetch many, similar tables and have created a configruration table which knows which data I need to fetch and how each table needs to be processed.
The tables will be appended to each other in one big fact table after processing is done.
I've created a function which can fetch such a table based upon the contents of the configuration table and pre-process each table individually.
And, I got it working even (!!) but with one remaining problem.
The problem is that I must combine the results with the config table itself before I can trim the table down into a more optimized one. There are many columns in the config table and many rows in the combined results, so this requires a large amount of RAM. I want to 'combine' or 'append' the result tables in one go instead because I believe this would be much faster.
I've been thinking about using 'Table.Combine' or appending them with '&', but can't get my head arround how to do this in a way which allows me to fetch and pre-process the result tables one-by-one.
Below a simplified but working example of what I want to do.
My question is:
How can I achieve the same result as below without attaching the results to the config table first?
Working example but attatching to config table
Hi Bamse
Oh yes, the new code editor are truly terrible.
Not sure if your idea will actually improve performance, but the code for it would be this:
let GetConfig = () => let Config = #table({"Param1", "Param2", "Param99"}, { { "Value A1", "Value A2", "Value A99" }, { "Value B1", "Value B2", "Value B99" } }) in Config, GetTable = (Param1, Param2, Param99) => let ResultTable = #table({"Column"}, { { "Demo " & Param1 & ", " & Param2 & ", " & Param99 } }) // Here some heavy lifting (merge, group e.c.t. is done and data is read from different sources) // I want to keep this isolated to the 'small' tables instead of doing this all at once on a massive table. in ResultTable, Custom1 = Table.ToRecords(GetConfig()), Custom2 = List.Transform( Custom1, each GetTable([Param1], [Param2], [Param99]) ), Custom3 = Table.Combine(Custom2) in Custom3But I would try the following: Reduce the number of function parameters by putting them all together into one record like so:
let GetConfig = () => let Config = #table({"Param1", "Param2", "Param99"}, { { "Value A1", "Value A2", "Value A99" }, { "Value B1", "Value B2", "Value B99" } }) in Config, GetTable = (MyParameterRecord) => let ResultTable = #table({"Column"}, { { "Demo " & MyParameterRecord[Param1] & ", " & MyParameterRecord[Param2] & ", " & MyParameterRecord[Param99] } }) // Here some heavy lifting (merge, group e.c.t. is done and data is read from different sources) // I want to keep this isolated to the 'small' tables instead of doing this all at once on a massive table. in ResultTable, Custom1 = Table.ToRecords(GetConfig()), Custom2 = List.Transform( Custom1, each GetTable(_) ), Custom3 = Table.Combine(Custom2) in Custom3
7 Replies
Calling in ImkeF
Hi ImkeF ,
I tried to post the code in the original, but the formatting is somewhat horrible since PowerQuery not recognised as a language.
But here is the code :
let
GetConfig = () =>
let
Config = #table({"Param1", "Param2", "Param99"},
{
{ "Value A1", "Value A2", "Value A99" },
{ "Value B1", "Value B2", "Value B99" }
})
in
Config,
GetTable = (Param1, Param2, Param99) =>
let
ResultTable = #table({"Column"},
{
{ "Demo " & Param1 & ", " & Param2 & ", " & Param99 }
})
// Here some heavy lifting (merge, group e.c.t. is done and data is read from different sources)
// I want to keep this isolated to the 'small' tables instead of doing this all at once on a massive table.
in
ResultTable,
// So here the tables are appended to each other as a column in the configuration table.
// By expanding them and dropping the configuration columns I get the result I want.
// But... The amount of data I am fetching significant an the config table is too.
// So I do NOT want to use a HUGE lot of RAM by attaching the tables to the config table first.
// Instead I want to append all "ResultTable's" to each other in one go.
CombinedTables = Table.AddColumn(GetConfig(), "Result", each GetTable([Param1], [Param2], [Param99])),
#"Removed Columns" = Table.RemoveColumns(CombinedTables,{"Param1", "Param2", "Param99"}),
#"Expanded Result" = Table.ExpandTableColumn(#"Removed Columns", "Result", {"Column"}, {"Result.Column"})
in
#"Expanded Result"Hi Bamse
Oh yes, the new code editor are truly terrible.
Not sure if your idea will actually improve performance, but the code for it would be this:
let GetConfig = () => let Config = #table({"Param1", "Param2", "Param99"}, { { "Value A1", "Value A2", "Value A99" }, { "Value B1", "Value B2", "Value B99" } }) in Config, GetTable = (Param1, Param2, Param99) => let ResultTable = #table({"Column"}, { { "Demo " & Param1 & ", " & Param2 & ", " & Param99 } }) // Here some heavy lifting (merge, group e.c.t. is done and data is read from different sources) // I want to keep this isolated to the 'small' tables instead of doing this all at once on a massive table. in ResultTable, Custom1 = Table.ToRecords(GetConfig()), Custom2 = List.Transform( Custom1, each GetTable([Param1], [Param2], [Param99]) ), Custom3 = Table.Combine(Custom2) in Custom3But I would try the following: Reduce the number of function parameters by putting them all together into one record like so:
let GetConfig = () => let Config = #table({"Param1", "Param2", "Param99"}, { { "Value A1", "Value A2", "Value A99" }, { "Value B1", "Value B2", "Value B99" } }) in Config, GetTable = (MyParameterRecord) => let ResultTable = #table({"Column"}, { { "Demo " & MyParameterRecord[Param1] & ", " & MyParameterRecord[Param2] & ", " & MyParameterRecord[Param99] } }) // Here some heavy lifting (merge, group e.c.t. is done and data is read from different sources) // I want to keep this isolated to the 'small' tables instead of doing this all at once on a massive table. in ResultTable, Custom1 = Table.ToRecords(GetConfig()), Custom2 = List.Transform( Custom1, each GetTable(_) ), Custom3 = Table.Combine(Custom2) in Custom3
