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

    Hi Mic1979 

    Hi

    May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

    Thank you.