Forum Discussion
Bamse
6 years agoFrequent Visitor
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 ...
- 6 years ago
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