How can I improve query performance?

Question

I am running the following query against a source table that is 3 columns by 37,636 rows and it is taking hours to run. How might I improve the performance?

The source table is, itself, the result of a precedent query, in case that matters. The precedent queries execute in a reasonable amount of time (in minutes) but this one bogs down badly.

The source table's column names are Columns, Columns2, and Count.

let
Source = Table.Buffer(GetWordCounts),
#"Grouped Rows" = Table.Buffer(Table.Group(Source, {"Count"}, {{"AllData", each _, type table}})),
Custom1 = Table.Buffer(Table.MaxN(#"Grouped Rows","Count",10)),
#"Expanded AllData" = Table.ExpandTableColumn(Custom1, "AllData", {"Columns", "Columns2"}, {"Columns", "Columns2"})
in
#"Expanded AllData"

I tried adding Table.Buffer in a few places, as you can see, but I can't tell if it is helping or hurting.

Any ideas?

MarcelBeug MarcelBeug · Accepted Answer · 2017-06-19T18:48:16

With the same amount of test data in Excel, it runs with me within about 2 seconds. If you isolate the input for this query in a separate Excel worbook or pbix file, do you still have the same bad performance? I suspect the bad performance is due to previous steps.

Edit: alternative code you may give a try:

let
    Source = Table.Buffer(GetWordCounts),
    #"Removed Duplicates" = Table.Distinct(Source, {"Count"})[Count],
    Max10 = List.Sort(#"Removed Duplicates",Order.Descending){9},
    #"Filtered Rows" = Table.SelectRows(Source, each [Count] >= Max10)
in
    #"Filtered Rows"

How can I improve query performance?

1 Answers