---
title: "Using query folding to optimise Luminesce queries in Power BI Desktop"
slug: "using-query-folding-to-optimise-luminesce-queries-in-power-bi-desktop"
updated: 2024-08-21T07:15:06Z
published: 2024-08-21T07:15:06Z
canonical: "support.lusid.com/using-query-folding-to-optimise-luminesce-queries-in-power-bi-desktop"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://support.lusid.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Using query folding to optimise Luminesce queries in Power BI Desktop

Query folding is the ability for a Power BI Desktop Power Query to use a single `SELECT` query statement to retrieve and transform data.

With query folding, data is both retrieved and transformed [at the source](/v1/docs/understanding-the-luminesce-platform-architecture). Without query folding, excess data may be fetched from the source before being separately transformed within the Power BI Desktop Power Query engine. This may lead to hefty increases in the time it takes for a request to be processed.

For example, you might write the following query to retrieve the first 100 transactions from a particular portfolio...

```sql
select * from Lusid.Portfolio.Txn
where PortfolioScope = 'Finbourne-Examples'
and PortfolioCode = 'Global-Equity'
limit 100
```

...and want to connect to the data via DirectQuery (that is, dynamically querying the data source rather than importing data that is then queried):

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/f4be78b6-121a-4880-8852-cdec238fac87.png)

Once you have connected to the data source, you can **Load** the data and begin efficiently building visualizations, such as the following example, by only accessing the data you need.

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/86a198c1-3d06-45ea-b2ef-a7a07a374ceb.png)

As you used a single `SELECT` query statement in the underlying native query, you can ‘fold’ over it, for example by applying a filter on a column in Power BI's Power Query Editor:

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/8615839f-b4ae-4166-b8f8-b5434b347797.png)

Query folding works by generating a wrapping `SELECT` statement which the base native query is then nested within. For the example above, the base native query might be wrapped inside of the filtered query as follows:

```sql
select "LusidInstrumentId",
    "TradePrice",
    "Units"
from
(
    select ^ from Lusid.Portfolio.Txn
where PortfolioScope= 'Finbourne-Examples'
and PortfolioCode = 'Global-Equity'
limit 100
)
where "TradePrice" >= 2.5
and LusidInstrumentId != 'LUID_ZZZZZZZZ'
and LusidInstrumentId != 'CCY_GBP';
```

Not all operations are currently supported for query folding. If you are having trouble when filtering or performing other operations, it is recommended to check the native query and remove areas which can interfere with query folding, for example `ORDER BY` clauses or `GROUP BY` statements.

Query folding is supported in version `1.x` and above of the Luminesce Power BI Connector when using the **Luminesce (Beta)** data source. [See how to update to the latest version of the connector.](/v1/docs/installing-the-power-bi-connector-to-connect-power-bi-desktop-to-luminesce)

[Read more on query folding in the Power BI help documentation.](https://learn.microsoft.com/en-us/power-query/query-folding-examples)

## Using dynamic M query parameters for efficient filtering

You can further refine your control over the data being retrieved and transformed by using [dynamic M query parameters](https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters#create-and-use-dynamic-parameters) in your query. This can ensure that filter selections in Power BI Desktop incorporate into your base query at the right point to improve query performance.

You can create a new parameter for your query in the **Power Query Editor** via **Manage Parameters**. Once created, you can add the dynamic M query parameter to your query via the **Advanced Editor**, for example:

```sql
let
  FromEffectiveAt = Date.ToText(Date.From(ParameterStartDate), [Format="yyyy-MM-dd", Culture="en-GB"]),
  Sql = Text.Combine({
    "select * from Lusid.Portfolio.Txn where PortfolioScope = 'Finbourne-Examples'
    and FromEffectiveAt = '", FromEffectiveAt, "'"
  }),
  Source = Value.NativeQuery(Luminesce.Platform("<your-domain>",
    [CommandTimeout=null, UseMetadataCache=null, UseQueryCache=null, QueryCacheDuration=null])
    {[Name="Luminesce",Kind="Database"]}[Data],
    Sql, null, [EnableFolding=true])
in
  Source
```

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/eaa6ad31-0e7c-4313-863d-d0d7347c2ad5.png)

To provide values for the parameter, you need to create a table of values which you can then bind to your query parameter and use to slice and filter data. [Read more on using dynamic M query parameters in the Power BI help documentation.](https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters)