Forum Discussion

Mic1979's avatar
Mic1979
Post Partisan
1 year ago
Solved

Improve performances for Nested.Join

Dear all,   I have a two tables: Table 1, which is the main one Table 2's, i.e. a lot of subtables from where I need to recover values to make calculation in the Table 1. I am using a custom f...
Need Help
  • v-karpurapud's avatar
    v-karpurapud
    1 year ago

    Hi Mic1979 

    After reviewing the dataset, it was found that the file contains a large amount of data, which could impact the performance of the functions. To enhance performance, follow these steps:

     

    • Use Direct Query or Live connection for importing data this will reduce the time and improves the query performance .
    • Remove unnecessary rows or columns from tables. This will help speed up operations, especially when performing joins.
    • Since you are using custom function due to this the performance will reduced. If possible, try to replace your custom function with built-in functions that are often optimized for performance.

      If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos".
Chewdata's avatar
Chewdata
Responsive Resident
1 year ago

Hey!

It is hard to see where you can improve without seeing the data :). Here are some general tips that might help improve the performance of the queries that I use. 

1. Split up your filter query or create a seperate filter query
If you need to do a lot of query steps in the helper query that are not neccesary for the join with the main table. Then it might help to split the query of the helper query into multiple querys. You can also create a seperate query that you use only for the join. Do the join before the table becomes too complex.
Example of a splitted query:

 

Helpertable_stage_1 = 

let
// Use this query for the inner join with the other table.
    Source = MYSOURCE,
    RemovedColumns = Table.RemoveColumns(Source,{"Column1"}),
    FilteredRows = Table.SelectRows(RemovedColumns, each ([Option] <> "C"))
in
    FilteredRows

 

HelperTable_stage_2 =

let
// Use this query to do the more complex calculations that are not nessecary for the joins
    Source = Helpertable_stage_1,
    add_ValueTimesTwo = Table.AddColumn(Source, "ValueTImesTwo", each [Value] * 2, Int64.Type)
in
    add_ValueTimesTwo

 

2. See if you can change the order for when the join takes place.
Sometimes you can change the moment you perform the inner you in the query steps. This also might improve the performance.

Hopefully this helps with the perfomance of you queries!