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

     

     

     

     

     

Jimmy801's avatar
Jimmy801
Icon for Community Champion rankCommunity Champion
5 years ago

Hello SaaM 

 

do you how long it takes to load after the join? Do you there some other transformation of the joined data? What data you are using out of the joined data?

Could you post the M-code here `?

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

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

    Hello,

     

    Here is my M-code for one of the queries to create my columns and it takes too much time (around 10 mn)

    I follow the same steps for other columns

    Just for information my Table1 takes around 50 sec to add the 38 columns to the original source

     

    my key_1 and key_2 are Text and I sum quantities

            #"join_sum_T1_T2_" = let

            #"GroupedLines" = Table.Group(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",

     

    The equivalent SQL code would be:

            with GroupedLines as (select key, sum(field_1) as sum_field_1, sum(field_2) as sum_field_2, sum(field_3) as sum_field_3, sum(field_4) as sum_field_4 
        from Table1
        where filter_item = 1
        group by key)
        select * from Table2_source
        left join GroupedLines
        on key_1 = key_2

     

    Thanks

     

    Kind regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      If adding 38 columns takes 50 ", the 10 'depends on the rest of the code not on recalculating the columns. try to strip the table.SelectRows and table select column statements from within the table.group and buffer the last table before grouby.

       

    • Jimmy801's avatar
      Jimmy801
      Icon for Community Champion rankCommunity Champion
      5 years ago

      Hello SaaM 

       

      you are referencing 2 external tables. One is the Table1 that takes 10 seconds to load. What about the table Table2_source? How long this takes to load?

      At the end your final query needs to load both tables and then apply also your steps. 

      I also tried to apply some changes to your code. Check out if this helps also

              #"join_sum_T1_T2_" = let

            BufferTable1 = Table.Buffer(Table.SelectColumns(Table.SelectRows(Table1, (sel)=> sel[#"filter_item"] = 1),{"key_1", "field_1", "field_2", "field_3", "field_4", "field_5"})),
BufferTable2 = Table.Buffer(#"Table2_source"),
#"GroupedLines" = Table.Group(BufferTable1 , {"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(BufferTable2 , {"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",

      If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
      Kudoes are nice too

      Have fun

      Jimmy