Microsoft optimization suggestion - using a shared query

[jaryszek]

Hi Guys,

this is the asnwer which i got from microsoft to use python query approach in power bi:

2. If you still prefer to use Python to retrieve the value of the environment variable, please create a shared query for each environment variable and then reference this shared query in all other queries.

 

Step1: Create a shared query (e.g. SyntheticBasePath)

let

    path = fnGetPath(ParamCostMgmtSyntheticPath)

in

    path

Step2: Reference SyntheticBasePath in other queries

 

let

    Source = Csv.Document(

        File.Contents(SyntheticBasePath & "BudgetFact.csv"),

        [Delimiter = ",", Columns = 12, Encoding = 1252, QuoteStyle = QuoteStyle.None]

    )

in

    Source

cam anybody explain how this is working and why this is better approach?

Best,
Jacek

[v-veshwara-msft]

Hi @jaryszek ,

Thanks for posting in Microsoft Fabric Community.

When you define a shared query like SyntheticBasePath, Power Query evaluates it once and stores the result in memory. Other queries that reference this shared query will use that value directly without recalculating it. This helps avoid hardcoding values like file paths in multiple places and makes the solution easier to maintain if the environment changes.

This approach follows best practices in Power Query. As mentioned by @DataVitalizer ,defining reusable queries or parameters instead of duplicating logic improves clarity and reduces the chance of errors when updates are required.

Please refer to this for best practices in Power Query: Best practices when working with Power Query - Power Query | Microsoft Learn

Hope this helps. Please reach out for further assistance.

Thank you.

[DataVitalizer]

Hi @jaryszek 

It’s better because it keeps things clean and modular, by using that apporach you will be defining the environment specific path once, then reference it across queries, no hardcoding, no repetition, and if the path changes, you only update it in one place.

It’s easier to manage, less error-prone, and much more scalable across projects or environments.

Did it work ? 👍 A kudos would be appreciated ‌‌📢 Mark it as a solution to help spreading knowledge

[jaryszek]

Thank you, so it means that 

SyntheticBasePath

will be used only once at the top of other queries? And order will be :

1) calculate shared query
2) put it into memory
3) used by other queries 

??

Best,
Jac

[DataVitalizer · Accepted solution]

Hi @jaryszek 

Sorry for the delayed reply. 

Absoluletly, that would be the right order of query execution.


Did it work ? 👍 A kudos would be appreciated ‌‌📢 Mark it as a solution to help spreading knowledge

[jaryszek]

thank you!