Forum Discussion

SaaM's avatar
SaaM
Icon for Helper II rankHelper II
5 years ago
Solved

Duplicate or refer Table ? Or someting else

Hi All,   i am facing terrible performance in a power query table after a couple of joins and transformation. Let me give you a broad overview over the steps i do. It would be great to find out w...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    No!

    You have to use a separate expression for 

    Table.SelectColumns(Table.SelectRows(Table1, (sel)=> sel[#"filter_item"] = 1)),{"key_1", "field_1", "field_2", "field_3", "field_4", "field_5"})

     

    for instance:

     

     

    TABBUFF=Table.Buffer(Table.SelectColumns(Table.SelectRows(Table1, (sel)=> sel[#"filter_item"] = 1)),{"key_1", "field_1", "field_2", "field_3", "field_4", "field_5"}),

     

     

     

    then the rest (check the sintax: ican't do)

     

     

    #"join_sum_T1_T2_" = let #"GroupedLines" = Table.Group(TABBUFF, {"key", "filter_item"}, {{"sum_field_1", each List.Sum([#"field_1"]), type nullable number}, {"sum_field_2", each List.Sum([field_32), type nullable number}, {"Ssum_field_3", each List.Sum([#"field_3"]), type nullable number}, {"sum_field_4", each List.Sum([#"field_4"]), type nullable number}}), #"MergedQueries" = Table.NestedJoin(#"Table2_source", {"key_2"}, 
#"GroupedLines", {"key_1"}, "Table1_sum", JoinKind.LeftOuter), 
#"Tableexpanded" = Table.ExpandTableColumn(#"MergedQueries", "Table1_sum", {"sum_field_1", "sum_field_2", "sum_field_3", "sum_field_4"}, {"sum_field_1", "sum_field_2", "sum_field_3", "sum_field_4"}) 
in #"Tableexpanded",

     

     

     

     

     

SaaM's avatar
SaaM
Icon for Helper II rankHelper II
5 years ago

Jimmy801  Anonymous 

I tried the Table.buffer but it takes the same time to evaluate all the queries.

I will try to refactor my previous code to optimize its performances

 

Thanks anyway

 

Kind regards

Saam

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    But before using table.buffer did you strip this piece of code from inside the table.group function?

     

    Table.SelectColumns(Table.SelectRows(Table1, (sel)=> sel[#"filter_item"] = 1),{"key_1", "field_1", "field_2", "field_3", "field_4", "field_5"})

     

     Can you show the code you used?

    • SaaM's avatar
      SaaM
      Icon for Helper II rankHelper II
      5 years ago

      The code I used is:

       

      #"join_sum_T1_T2_" = let #"GroupedLines" = Table.Group(Table.Buffer(Table.SelectColumns(Table.SelectRows(Table1, (sel)=> sel[#"filter_item"] = 1)),{"key_1", "field_1", "field_2", "field_3", "field_4", "field_5"}), {"key", "filter_item"}, {{"sum_field_1", each List.Sum([#"field_1"]), type nullable number}, {"sum_field_2", each List.Sum([field_32), type nullable number}, {"Ssum_field_3", each List.Sum([#"field_3"]), type nullable number}, {"sum_field_4", each List.Sum([#"field_4"]), type nullable number}}), #"MergedQueries" = Table.NestedJoin(#"Table2_source", {"key_2"}, #"GroupedLines", {"key_1"}, "Table1_sum", JoinKind.LeftOuter), #"Tableexpanded" = Table.ExpandTableColumn(#"MergedQueries", "Table1_sum", {"sum_field_1", "sum_field_2", "sum_field_3", "sum_field_4"}, {"sum_field_1", "sum_field_2", "sum_field_3", "sum_field_4"}) in #"Tableexpanded",

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        No!

        You have to use a separate expression for 

        Table.SelectColumns(Table.SelectRows(Table1, (sel)=> sel[#"filter_item"] = 1)),{"key_1", "field_1", "field_2", "field_3", "field_4", "field_5"})

         

        for instance:

         

         

        TABBUFF=Table.Buffer(Table.SelectColumns(Table.SelectRows(Table1, (sel)=> sel[#"filter_item"] = 1)),{"key_1", "field_1", "field_2", "field_3", "field_4", "field_5"}),

         

         

         

        then the rest (check the sintax: ican't do)

         

         

        #"join_sum_T1_T2_" = let #"GroupedLines" = Table.Group(TABBUFF, {"key", "filter_item"}, {{"sum_field_1", each List.Sum([#"field_1"]), type nullable number}, {"sum_field_2", each List.Sum([field_32), type nullable number}, {"Ssum_field_3", each List.Sum([#"field_3"]), type nullable number}, {"sum_field_4", each List.Sum([#"field_4"]), type nullable number}}), #"MergedQueries" = Table.NestedJoin(#"Table2_source", {"key_2"}, 
#"GroupedLines", {"key_1"}, "Table1_sum", JoinKind.LeftOuter), 
#"Tableexpanded" = Table.ExpandTableColumn(#"MergedQueries", "Table1_sum", {"sum_field_1", "sum_field_2", "sum_field_3", "sum_field_4"}, {"sum_field_1", "sum_field_2", "sum_field_3", "sum_field_4"}) 
in #"Tableexpanded",