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 function:

(
Input_Table as table,
Distribution_Table as table,
Input_Table_Helper as text,
Distribution_Table_Helper as text,
Distribution as text
) =>

let
// Merge and Expand
Merged_Table = Table.NestedJoin (
Input_Table,
Input_Table_Helper,
Distribution_Table,
Distribution_Table_Helper,
Distribution,
JoinKind.LeftOuter
),

Input_Table_Headers = Table.ColumnNames(Input_Table),
Distribution_Table_Headers = Table.ColumnNames(Distribution_Table),
List_Header_Difference = List.Difference(Distribution_Table_Headers,Input_Table_Headers),

Expanded_Merged_Table = Table.ExpandTableColumn (
Merged_Table,
Distribution,
List_Header_Difference,
List_Header_Difference),

// Multiply
New_Volumes = Table.AddColumn(Expanded_Merged_Table,"New Y3 Volumes", each ([Y3] * [Distribution]), type number),
Removed_Columns = Table.RemoveColumns(New_Volumes,{"Y3", "Distribution"}),

// Rename Colum
Renamed_Y3 = Table.RenameColumns(Removed_Columns,{{"New Y3 Volumes", "Y3"}}),

// Round Y3
Round_Y3 = Table.TransformColumns(Renamed_Y3,{
{"Y3", each Number.Round(_, 4), type number}})

in Round_Y3

 

with Table.NestedJoin to make this operation, invoked several times.

As soon as the Table1 becomes more complex, I see a decrease of performances of the query and a huge increase of the time required.

I am wondering is there is another way to make this faster, I was thinking of the usage of the Records structure of the List, or something else.

 

Thanks for your help.

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".

9 Replies

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

    • Mic1979's avatar
      Mic1979
      Post Partisan
      1 year ago

      Hello,

      on what table and on what step of the query?

      And additionally, what is the better Bufer.Mode to be used? In some cases I saw my performances got worst with Table.Buffer.

       

      Thanks. 

  • PwerQueryKees's avatar
    PwerQueryKees
    Super User
    1 year ago

    If your "Table2" all have the same columns (in as far as you are usinbg them), first do a Table.Join({Table1, Table2, Table3, etc.})

    Then use the combine tbale in the merge.

    If that is not suitable for your use case, give us some more details. Like the structure of the various tables, the expected number of records and some usable (NO SCREENSHOT) sample data both before and the result.

    • PwerQueryKees's avatar
      PwerQueryKees
      Super User
      1 year ago

      Typo! I meant a Table.Combine() ! Not a Table.Join()... ğŸ™„

  • v-karpurapud's avatar
    v-karpurapud
    Community Support
    1 year ago

    Hi Mic1979 
    Thanks for reaching out to the Microsoft Fabric Community Forum.

    To enhance the performance of your queries, these are a couple of techniques that might useful:
    1. Optimize this process by filtering the tables before the join operation, reducing the number of rows that need to be processed.
    2.Merge Queries: If possible, merge the subtables into a single table before joining with Table 1. This can sometimes lead to better performance than multiple NestedJoin operations.

    3. Alternative approach instead of using Table.NestedJoin to merge and expand the tables use Record.Lookup as this is faster for key-based lookups.

    If the above steps doesn't work fine, to  address your concern effectively, we kindly request additional data so that it will better to understand deeply.

    If you have any further questions or need additional help with this, feel free to reach out  to us for further assistance!

    If you find this post helpful, please mark it as an "Accept as Solution" and give a KUDOS.

    • Mic1979's avatar
      Mic1979
      Post Partisan
      1 year ago

      Hello

       

      here thel link to the sample file:

       

      https://docs.google.com/file/d/1RYJjR0_YDk3wYp6tIs-5VrIADvRveUVa/edit?usp=docslist_api&filetype=msexcel

       

      Basically what I did with Teble.Nestedjoin is:

      1. From the Table Project_Step_DISTRIBUTION I did the merge with Function_DISTRIBUTION.
      2. I have rows repeated in the column to merge for the table Function_DISTRIBUTION. I need this as I need to expand the column Function_Description to have this column present in the resulting table. In fact this is not a simple lookup.
      3. Then I am using custom function  Merge_Multiply_Rename, basically doing the same thing, but with always the same structure in the table.
      4. The final table I have is at the following link: https://docs.google.com/file/d/1KXoWZYEkkqZyASufvHGeVn39gpTzFjfW/edit?usp=docslist_api&filetype=msexcel

         Here I need to group by summing the column TOTAL_Volumes. But the GroupBy in Power query is       extremely low. So I need to find an alternative to this with a custom function.

      Hoping what I wrote is clear enough for you to support me.

       

      THANKS IN ADVANCE!!!

       

      • v-karpurapud's avatar
        v-karpurapud
        Community Support
        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".