cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bamse
Frequent 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 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 tableWorking example but attatching to config table

1 ACCEPTED SOLUTION
ImkeF
Super User II
Super User II

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

7 REPLIES 7
ImkeF
Super User II
Super User II

Hi @Bamse ,

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Greg_Deckler
Super User IV
Super User IV

Calling in @ImkeF 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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
Super User II
Super User II

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Bamse
Frequent Visitor

@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
Super User II
Super User II

Thanks for coming back, @Bamse!

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Bamse
Frequent Visitor

Hi @ImkeF ,


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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors