A file is moved, a new server or database used, the filename is changed,….
So many ways that a connection in Power BI can fail suddenly or that you need to change it in anticipation of a change. No problem if you have just a few queries, but what if that amount is higher?
That is exactly what I want to avoid as I don’t like to do repetitive work if I don’t have to. I suspect I am not the only one and others will want to work less too. No, sorry, more efficiently.
I have 2 ways that I tend to use, depending on the specific setup of the model. One is to use parameters and the other is to use a source-query. Both can be used separately or in combination.
Let’s first take a look at how it all looks without any setup.
Setup without parameter and source file
As an example I have made a list of all the deaths of Sean Bean on screen, which gives me one fact and 3 dimensions, which isn’t a lot but enough to make the case.
You can see below these give us a little star schema, always make a star schema by the way, it can’t be said enough.
As many people will do, I started out ‘working quickly’ and duplicated the query to adjust it for each table I needed. Each of the 4 tables starts with a link to the source, as you can see below.
This works, we are done.
Except when we need to change the source for some reason. I now need to make this change in each of the queries I created. Being lazy efficient, I like to opt for another solution.
Parameter
One way of doing this, is by using a parameter that contains the location of the file.
First we need to create a parameter, you can do this by clicking on the ‘manage parameters’ button
Here you click on ‘New’, which you give a descriptive name and perhaps a description. Don’t forget to set the type to ‘Text’ of you might run into troubles when you want to change it after publishing.
Now that we have the parameter, we go to the source in our queries and switch out the file for the parameter. From now on, if we change the location in the parameter, it is automatically changed in each query where we do this.
Use source file
In the case of the deaths of Sean Bean, I work from one file and create my star schema by pulling out the fact and dimensions from it. This means there is another trick to I can use now.
We want to end up the same way as before, with our 4 tables. You can use existing tables and rework everything to fit this setup, but it is easier if you do it from the start.
We begin by pulling in the file, just as before. We start by making all the changes that are valid for all queries. In the case of Sean Bean, we split the ‘movie title (year of release) column into two separate ones. This can either result in many steps before you continue or in just the connection to the file.
To make sure that this source file isn’t shown in the report we need to turn off ‘Enable load to report’. We want to pull the tables for the report from this one, not use it directly.
Once that is done, we can reference this table instead of duplicating it. This means that all the applied steps in this source query are also applied in the following queries.
The fact table has no other steps applied, but the dimensions do have a few extra. The main advantage now is that you can change the connection in the source query and this change will be propagated to the other queries, again making it easier to change the connection.
Combining both
We now saw 2 tricks to make it easier to change a connection in our queries, but nothing stops us from combining the 2 into an easy solution. Using the source query solution makes sure that we only have to check the connection once if there is ever a need to debug it and the parameter provides an easy way to change the connection details if needed.
Do you need to use this complete setup every time you create a Power BI model? Off course not, it is always best to evaluate each situation separately and make a decision based on your assessment. In most cases with a single file as source I only use the option with the source query. The parameters I tend to use more when I expect that the connection might change.
These are simple tricks once you get the hang of it and will absolutely make your life easier when incorporated in your habits. Small changes with big impact, as impactful as, well, the arrows in Sean Bean’s favourite death scene:
ABOUT THE AUTHORS
Jo David
Power BI expert & BI Consultant
Discover more content from our blog
Connecting the Dots: Neo4j Graph Intelligence Inside Microsoft Fabric
Microsoft Fabric unifies data engineering, analytics, and AI across the Microsoft ecosystem.
But many business problems like fraud detection, customer 360, supply-chain optimization, … depend not on single data points, but on how those points connect.
Microsoft Fabric Ideation Session
The Microsoft Fabric Ideation Session helps organizations move from curiosity to clarity. In just three hours, our experts guide your team through Fabric’s AI and analytics capabilities to identify high-impact use cases, connect business goals with technology, and build a practical roadmap for value-driven transformation.
Exploring Microsoft Fabric: Workshop at Tallinn Data Week
Discover the power of Microsoft Fabric for unified analytics. Learn how to combine data integration, data science, real-time analytics, and business intelligence in a single platform.
From Data Chaos to AI-Readiness: Sparkle’s Adaptive Data Management Framework
Every company today wants to harness the power of AI. From copilots that accelerate daily work to generative models that reveal new opportunities, AI promises to transform how businesses operate. But there’s one constant truth: AI is only as good as the data it consumes.
Microsoft Fabric: From Reporting to AI-driven operations
Microsoft Fabric unifies data, analytics, and AI on a single platform, simplifying management and enabling real-time, AI-driven insights. Organizations can move from reporting to automated processes, unlocking efficiency, predictive insights, and smarter business decisions. Planning the transition now ensures a smooth, cost-effective adoption.
Power Bi Input Methods in Microsoft Fabric: Use Cases and Technical Solutions
Organizations using Microsoft Fabric with Power BI increasingly need ways to capture user input, not just analyze existing data. Until recently, the main native options were Power BI custom visuals and Power Apps. Since May 2025, a third option- Fabric Translytical Flows- has become available. This tutorial compares these three approaches to help you choose the right solution for your scenario.
