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
Webinar: Medallion Architecture
Join us for an insightful and interactive webinar where industry expert Piethein Strengholt will explore the foundations and practical applications of the Medallion Architecture.
Originally introduced by Databricks, this architecture has become a crucial component for modern data platforms, and our exclusive session will break down its key concepts.
Sparkle’s Power Automate Solution Transforms Data Management for KOV
KOV partnered with Sparkle to automate their manual workflows for managing administrative tasks. This improved efficiency, reduced errors, and set the stage for future scalability.
How to easily manage sources in Power BI
Avoid repetitive work when sources change in Power BI! By using parameters to centralize file paths or source queries to manage transformations, you can save time and simplify updates.
Whether working with multiple queries or building a star schema, these techniques ensure efficient, adaptable models. Combine both methods for even greater flexibility. Small changes, big impact!
Anneli’s story: Embracing change – a journey of transformation
From city life to the countryside, my journey led me from diverse roles to a fulfilling career in IT. Now at Sparkle, I’ve found a team that shares my values and a role that lets me grow while making an impact.
Webinar: From Innovation to Regulation – the European AI Act
Join us for an exclusive online webinar: the European AI act and its Impact on Intellectual Property.
WIDEN’s IT/IP experts from Latvia, Estonia, and Lithuania in collaboration with Sparkle, a strategic data consultancy firm, are bringing together the best minds in the Baltics to explore one of the hottest topics in tech today and the impact of the European AI act.
Lord of the Rings organization styles for Power BI
Check your Power BI organization according to the world of Tolkien: are you organized like Hobbits, Orcs, Dwarves or Sauron?
This unique approach explained by our Sparkleer Jo uses the legendary journey of Frodo Baggins to guide you through different organizational structures. Discover how to create a harmonious Power BI environment for your business.
