Faster append query

[Anonymous]

Hello all!

So I'm working on this report that calls from a SQL server. There are millions of rows in each of these tables. The format as follows:

dbo_table_for_production_line_1:

part_id | Batch_num | part_weight | <lots of other stuff that we don't care about>

00001  | 919191919  |          350     |

dbo_table_for_production_line_2:

part_id | Batch_num | part_weight | <lots of other stuff that we don't care about>

00002  | 818181818  |          352     |

dbo_table_for_production_line_3:

part_id | Batch_num | part_weight_D1 | part_weight_D2 | <lots of other stuff that we don't care about>

00003  | 717171717  |          342          |           392          |

Now what I do, is I group by the batch number. I need the Min, Max, and Avg of part weight, and if it's on production line 3, I need that second part weight as well.

So what I've done is make a query based on each of the dbo_table_for_production_line_X that looks like this:

let
    Start_Date =Excel.CurrentWorkbook(){[Name="Start_Date"]}[Content]{0}[Column1],
    End_Date = Excel.CurrentWorkbook(){[Name="End_Date"]}[Content]{0}[Column1],
    
    Source = Sql.Database("usdvenrpt01", "selfservicebi"),

    dbo_table_for_production_line_1 = Source{[Schema="dbo",Item="dbo_table_for_production_line_1"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(dbo_table_for_production_line_1,{"WorkOrder", "DoseWeight"}),
    #"Filtered" = Table.SelectRows(#"Removed Other Columns", each List.Contains(A_different_table[The_batch_nums_that_need], [Batch_num])),
    #"Grouped Rows" = Table.Group(#"Filtered", {"Batch_num"}, {
{"Weight Min D1", each List.Min([DoseWeight]), type number},
{"Weight Max D1", each List.Max([DoseWeight]), type number},
{"Weight Avg D1", each List.Average([DoseWeight]), type number}, 
{"Weight Max D2", each null, type number}
{"Weight Min D2", each null, type number}
{"Weight Avg D2", each null, type number}})  
in
    #"Grouped Rows"

And for Production Line 3, the agregation functions are set to be Min Max and Average of the other column instead of each null.

Each of these three queries loads really quickly and seems to calculate server side.

Now, I then append all three of these tables into one master table using:

let
    Source = Table.Combine({Line_1_query, Line_2_query, Line_3_query})
in
    Source

All of a sudden, the master table takes forever to load. So long that I don't even know how long it takes because after 30 min I decided to stop it.

I know that this last query is running client side because my CPU usage goes through the roof when I try to load it, but after filtering to only the Batch_num that I need, there is less than 24 Rows in all three tables combined, so I really don't know why this is throwing a tantrum.

[Anonymous]

Update:

I must have misunderstood the way that the "Refresh Preview" button works, or misunderstood the way that the tables update and refresh, because I now am suspicious that the three subqueries are running client side.

[v-juanli-msft]

Hi @Anonymous 

After you append queries together, you click on "refresh preview" button, right?

Please try to uncheck "enable load" or "included in report refresh" for the original queries.

In this way, the three original queries will not be loaded in the data model and it can reduce memory consumption.

Reference:

Performance Tip for Power BI; Enable Load Sucks Memory Up

Speeding up power query

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

If I do this, it will make working on this much easier for me, but I don't think that I can deliver a tool to my coworkers that takes 45 min to load the first time that they use it or change the parameters of filter in the Excel sheet (the information in those querries is liable to change somewhat regularly so they will need to be updated).

What I think I need is some way to tell which steps are running client side. I know of the trick where you right click on the step in the query and see if the "view native query" is greyed out or not, but unfortunately I don't think that it will work for me 100% of the time.

I have a step in several queries where I group by and then use a function like  "List.Mode" or "List.StandardDeviation" which are functions that aren't supported in the User Interface. These steps are always greyed out when I try to "View Native Code" and I think the reason is that the UI doesn't support those functions.

Is there any way to get a report of how much time each query takes to load? So for example, I could hit "refresh all" before I leave for the evening and return the next morning to see which subqueries needed how much time to refresh? Is there a way to break down that information into the steps within each subquery?

I don't think that your solution is a silver bullet, but thanks very much Maggie.

Best wishes,

MannyZ

---

---