Need help to optimize appending tables created from config table in Power Query

[Bamse]

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

[ImkeF]

Hi @Bamse ,

could you please post the code itself, so I can play with it? Thanks!

[Greg_Deckler]

Calling in @ImkeF 

[Bamse]

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"

[ImkeF · Accepted solution]

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

[Bamse]

@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!

[ImkeF]

Thanks for coming back, @Bamse!

[Bamse]

Hi @ImkeF ,

Thanks!
I am going to try both solutions and will report back which one performs best based upon the preview refresh time.