Performance problem at Table.RowCount

[JVos]

The query below has a performance problem at the last step: #"Counted Rows". I inserted this step to replace some others steps that gave performance problems, so I inserted the count because I had the idea that there were a lot of rows. However it takes a long, long time before the count is finished. And the result was 3.420 records, so not that much.

What can cause this problem? It's happening in Power Query within Excel (Office365 subscription).

let
    RoutingLinesQuality = Table.Buffer(qryRoutingLines_Quality),
    RoutingLinesFirst = Table.Buffer(qryRoutingLines_First),
    #"Merged Queries" = Table.NestedJoin(qryRoutingTransitions_All, {"ROUTING_ID_From"}, RoutingLinesQuality, {"ROUTING_ID"}, "RoutingLinesQuality", JoinKind.LeftOuter),
    #"Expanded RoutingLinesQuality" = Table.ExpandTableColumn(#"Merged Queries", "RoutingLinesQuality", {"RoutingLineId", "RouteName_edit", "RouteName", "ItemTo", "SupplyStatusTo"}, {"RoutingLinesQuality.RoutingLineId", "RoutingLinesQuality.RouteName_edit", "RoutingLinesQuality.RouteName", "RoutingLinesQuality.ItemTo", "RoutingLinesQuality.SupplyStatusTo"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded RoutingLinesQuality",{"ROUTING_ID_To"},RoutingLinesFirst,{"ROUTING_ID"},"RoutingLinesFirst",JoinKind.LeftOuter),
    #"Expanded RoutingLinesFirst" = Table.ExpandTableColumn(#"Merged Queries1", "RoutingLinesFirst", {"RoutingLineId", "RouteName", "ItemFrom", "SupplyStatusFrom"}, {"RoutingLinesFirst.RoutingLineId", "RoutingLinesFirst.RouteName", "RoutingLinesFirst.ItemFrom", "RoutingLinesFirst.SupplyStatusFrom"}),
    #"Merged Queries2" = Table.NestedJoin(#"Expanded RoutingLinesFirst",{"ROUTING_ID_From"},qryActiveRoutings_4FromTo,{"ROUTING_ID"},"RoutingFromDetails",JoinKind.LeftOuter),
    #"Expanded qryActiveRoutings_4FromTo" = Table.ExpandTableColumn(#"Merged Queries2", "RoutingFromDetails", {"RoutingName"}, {"RoutingFromDetails.RoutingName"}),
    #"Merged Queries3" = Table.NestedJoin(#"Expanded qryActiveRoutings_4FromTo",{"ROUTING_ID_To"},qryActiveRoutings_4FromTo,{"ROUTING_ID"},"RoutingToDetails",JoinKind.LeftOuter),
    #"Expanded qryActiveRoutings_4FromTo1" = Table.ExpandTableColumn(#"Merged Queries3", "RoutingToDetails", {"RoutingName", "PolicyName"}, {"RoutingToDetails.RoutingName", "RoutingToDetails.PolicyName"}),
    #"Merged Queries4" = Table.NestedJoin(#"Expanded qryActiveRoutings_4FromTo1",{"RoutingLinesFirst.RoutingLineId", "RoutingLinesQuality.RoutingLineId"},qryAllRoutingLinePredecessors,{"RoutingLineId", "Predecessor"},"qryAllRoutingLinePredecessors",JoinKind.LeftOuter),
    #"Expanded qryAllRoutingLinePredecessors" = Table.ExpandTableColumn(#"Merged Queries4", "qryAllRoutingLinePredecessors", {"RoutingLineId"}, {"qryAllRoutingLinePredecessors.RoutingLineId"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded qryAllRoutingLinePredecessors", {"RoutingLinesQuality.RoutingLineId", "RoutingLinesFirst.RoutingLineId"}),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Duplicates", "PredecessorPresent", each if [RoutingLinesQuality.RoutingLineId] = null then null else if [qryAllRoutingLinePredecessors.RoutingLineId] = null then "No" else "Yes"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"qryAllRoutingLinePredecessors.RoutingLineId"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"RoutingToDetails.PolicyName", "ROUTING_ID_To", "RoutingToDetails.RoutingName", "RoutingLinesFirst.RoutingLineId", "RoutingLinesFirst.RouteName", "RoutingLinesFirst.ItemFrom", "RoutingLinesFirst.SupplyStatusFrom", "DirectOrIndirect", "FromToType", "Set", "ROUTING_ID_From", "RoutingFromDetails.RoutingName", "RoutingLinesQuality.RoutingLineId", "RoutingLinesQuality.RouteName_edit", "RoutingLinesQuality.RouteName", "RoutingLinesQuality.ItemTo", "RoutingLinesQuality.SupplyStatusTo", "PredecessorPresent"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"RoutingToDetails.PolicyName", "Policy"}, {"ROUTING_ID_To", "ROUTING_ID"}, {"RoutingToDetails.RoutingName", "RoutingName"}, {"RoutingLinesFirst.RoutingLineId", "RoutingLineId"}, {"RoutingLinesFirst.RouteName", "RouteName"}, {"RoutingLinesFirst.ItemFrom", "ItemFrom"}, {"RoutingLinesFirst.SupplyStatusFrom", "SupplyStatusFrom"}, {"ROUTING_ID_From", "Pred_ROUTING_ID"}, {"RoutingFromDetails.RoutingName", "Predecessor_RoutingName"}, {"RoutingLinesQuality.RoutingLineId", "Predecessor_RoutingLineId"}, {"RoutingLinesQuality.RouteName_edit", "QualityRouteName_Old"}, {"RoutingLinesQuality.RouteName", "Predecessor_RouteName"}, {"RoutingLinesQuality.ItemTo", "Predecessor_ItemTo"}, {"RoutingLinesQuality.SupplyStatusTo", "Predecessor_SupplyStatusTo"}}),
    #"Add Custom" = Table.AddColumn(#"Renamed Columns1", "PredecessorWanted", each ""),
    #"Counted Rows" = Table.RowCount(#"Add Custom")
in
    #"Counted Rows"

[Anonymous]

Does the query perform fine without that Row count last step?

[JVos · Accepted solution]

I turned out to be a performance issue with one of the queries that are invoked. So this issue is closed.

[JVos]

When I open the Excel file and then open the query in the Query Editor, and start clicking on the first step, then second step, and so on, then first 1 or 2 minutes the system seems to be very busy so those first steps. After that I can click the steps subsequently and the results per step are displayed. I also can go back to one of the first steps and the results are quickly displayed.

However when I after that choose on the Excel sheet to refresh that data, it again takes a long while (5 to 10 minutes). Mashup Evaluation Container is taking about 25% of my processor capacity.

So it's maybe not the last step that is the problem. I also can't detect (a) certain other step(s) that are the problem.

The workbook has many other queries which are performing OK. 'Fast Data Load' is on, 'Enable background refresh' is off.