Complex query

[rdnguyen]

To complex query with multiple conditional joins; and several Sum or Count following group by after the join to extract more data; would you confidently arrange at Power Query or push back to SQL Server if any chance?

Kindly suggest with your best practices

Thanks and best regards.

Rich

[BA_Pete · Accepted solution]

Hi @rdnguyen ,

Absolutely 100% send to the SQL server instead of Power Query.

Almost all of the operations you've listed require Power Query to load the entire table into memory to perform, and therefore performance will be poor, especially if all these operations are done in the same query, one after another.

Nothing beats an SQL server at performing these types of operations.

However, with Power Query and the Power BI/DAX front-end you quite often don't need to do any of these things structurally at all. Properly constructed/optimised DAX on the VertiPaq engine will often outstrip or at least match SQL Server performance in many areas, depending on what you want to do.

Pete