What is faster Table.Join or Table.SelectRows when filtering hard-coded items? RRS feed

  • Question

  • What is faster, Merge or Select, when selecting hard-coded items?  And any idea when Select is faster than Merge or vice versa?
    In some cases I have a large 30 item list; and in others I have a small 3 item list.  When should I use Table.Join vs Table.SelectRows?

    For example, 

        Source = Excel.Workbook(File.Contents("C:\fruit.xlsx"), null, true),
        Source_Table = Source{[Item="Source",Kind="Table"]}[Data],
        Set_Type = Table.TransformColumnTypes(Source_Table,{"Name", type text}),
        Filtered_Rows = Table.SelectRows(Set_Type, 
                     each [Name] <> = "Apples" or [Name] = "Pears" or [Name] = "Oranges"), 
        Make_Table = #Table({"Fruit to select"}, { {"Apples"},  {"Pears"},  {"Oranges"} }),
        Filter_Table = Table.Join(Set_Type, "Name", Make_Table, "Fruit to select")    
    Thursday, October 17, 2019 12:52 PM


  • It depends on a number of variables. In some cases the merge operation gets transformed into a WHERE clause in the native query thanks to query folding, but you should give it a try with your unique scenario and let us know your findings.
    Friday, October 18, 2019 3:44 AM