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

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

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