Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
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
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
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
@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!
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
Hi @ImkeF ,
Thanks!
I am going to try both solutions and will report back which one performs best based upon the preview refresh time.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.