Limit the amount of time you watch Power BI getting rows of data when you are working on your views
me@jaykilleen.com wrote this about 7 years ago and it was last updated about 7 years ago.
After I create a new project for Power Query or Power BI I like to implement a parameter called 'ENV' that takes a list of values including 'Development' and 'Production'. It might look like this:
"Development" meta
[
IsParameterQuery=true,
List={"Development", "Production"},
DefaultValue="Development",
Type="Text",
IsParameterQueryRequired=true
]
I can then use this parameter to switch on Table.Limit functions that I place throughout my queries. In particular, I might have a query pulling down millions of rows of data from an SQL Server factSales table. This can take a very long time to load when I don't even know the final shape that my queries will take or how I want to present them in my view layer.
This can temporarily limit my results to a few 1000 rows of data instead of millions
This could look something like:
// SalesOrder
let
// Pull the Sales table from the SQL Server
Source = SQLServerQuery,
dbo_FactSales = Source{Item="FactSales"]}[Data],
// Implement Query Folding on the DateQueryFoldOnTransactionDate to limit the history
FilterDate = Table.SelectRows(dbo_FactSales, each
[TransactionDateKey] >= 20150000),
// Limit to first 1000 rows when in Development otherwise just use the result of the last step
Limit =
if ENV = "Development" then
Table.FirstN(FilterDate,1000)
else
FilterDate,
// Continue the transformation
MergeDate = Table.NestedJoin(
Limit, {"TransactionDateKey"},
Date, {"Key"},
"NewColumn",
JoinKind.Inner
),
ExpandDate = Table.ExpandTableColumn(
MergeDate,
"NewColumn",
{"PeriodKey"},
{"FinancialPeriodID"}
),
MergeCustomer = Table.NestedJoin(
ExpandDate, {"CustomerKey"},
Customer, {"Key"},
"NewColumn",
JoinKind.Inner
),
RemoveCustomer = Table.RemoveColumns(
MergeCustomer,
{"NewColumn"}
),
SortDate = Table.Sort(
RemoveCustomer,
{
{"TransactionDateKey", Order.Ascending}
}
),
// Buffer the table into memory to restrict other queries from pulling more data from the SQL Server
Buffer = Table.Buffer(SortDate)
in
Buffer
Now I can simply go back to my params group to the ENV parameter and switch the value to 'Production' when I am ready to pull in the full result.
As a further tip, you could create a query for SalesOrderDEV that applies the limit there instead of reevaluation the entire SalesOrder query. This would be useful if you found yourself regularly switching between environments. In my case, I would only do it a hand full of times whilst creating the initial .pbix file.
Be keen to know if anyone else performs these sort of steps to help improve performance.