Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Faster append query

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.

3 REPLIES 3
Anonymous
Not applicable

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.

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
Not applicable

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



 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.