Implementing an Environment Parameter in Power BI to Improve Performance in Development

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 over 4 years ago and it was last updated over 4 years ago.


← Back to the Posts

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.

Similar