Forum Discussion
Microsoft optimization suggestion - using a shared query
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
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
5 Replies
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 knowledgeThank 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,
JacHi 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
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.
