Forum Discussion
Need help to optimize appending tables created from config table in Power Query
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
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
Custom3
But 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
ImkeF
When it comes to performance, solution two might be slightly faster, but it's hard to be certain due to a large fluctuation in my test results.
But when it comes to elegance and maintainability, solution two is a clear winner.
Thanks!