Hi, amazing video. What I was doing before watching this video is: let's say that I have some data in excel file on sharepoint, so I'm adding the SharePoint source to the point where I can see all my files needed for the report, I usually call this query "SP source" and I leave it there. Then create next queries by referencing this "SP source" query. That way if I need to change the source, I just need to change this "SP source query".
I watched this two weeks too late! I just manually updated everything in one of my reports from XLSX to Sharepoint and this would have saved the day! From now on I'm watching your videos as the are published. Can't miss out on any more of these goodies!
I spent 3 hours today doing it the manual way and as I’m going to bed, I figured I’d scroll TH-cam. Lo and behold, I see this video in my recommendations. As much I wanna cry right now, I’m super excited to try this tomorrow with the remaining queries! Thanks Patrick
+1 more reason you guys are my favorite for learning Power BI. I can't tell you how many times this year alone that you all have taken my Power BI skills to the next level and impressed my boss.
Great video... I always use params to point to data sources, that way you can move from dev to uat to production with no pain... Set up the params as a list to make it super smooth.
I'm a little upset I didn't stumble upon this a month ago. We just went through a server migration and manually updated each connection string with a parameter.... for every single connection in every single report. !!!!!! This would have saved some time! I immediately shared with my team and my client for future reference after I just watched this. Thanks so much!
Wow. All this time I've been using parameters and never knew about this simple switch through change data source settings. Admittedly I only go there if there's a credential issue with my source. Thank you for sharing!!!
im been manually changing my source and finally found your video. this method is super efficient and it really save save my time, like a lot. this is insane. thanks you so much
I just always set up a “connection only” query to external data source to be reused. So maybe it’s “sqlconnection” as the query. Then every other one that needs that source is referenced off it (Source = sqlconnection). If the source changes, I only make 1 change to “sqlconnection” and boom, same result. This change can also be achieved in the data source settings as you showed, just no parameters. Not everybody knows parameters, so I find this pattern easier to understand. BUT I still really love this video and concepts you’re teaching. Keep it up!
I'm doing it with a custom function that reads a value out of an Excel named range. That advantage (or disadvantage depending on your perspective) is that the user sees the connection string in a range in Excel and can change it without knowing anything about PQ. However, I'm definitely going to use this (I had no idea it existed).
Can you please elaborate? In addition to changing the data base name I would also like to change the sql table name that is used in the query. Table name has date that changes each month
I don't want to get to overexcited but this works for dtaflows with a bit more of a workaround. I just created workspaceID and dataflowID parameters. and added my IDs for both. then added this into each query just the once. it looks like its working and now I can just change the parameters to my production dataflows rather than have to go through every query. thankyou so much
Great video - will this work in situations where the location is tied to an identity? For example if my filepath is: C:\users\myuserid, is there a way to ensure that someone else can jump in and refresh? Would I need to do something else, like a named cell that feeds into the parameter name where the user inputs their userid before refreshing?
That’s exactly where my mind went! Dev/test/prod/preprod/production, if you’ve got multiple data sources, that’s going to save a lot of time. Pity it’s fairly well hidden! Well done, Patrick!
Up to now I have been creating a query for each source, then referencing that query whenever I use that source, so changing the “source query” changes all the others. Using Parameters looks like it’s much cleaner.
It is like changing an excel data source in Excel PowerPivot data model. There you can have several references to the same file but under existing connections it is easy to change. Thanks for the tip!
Q: is there a way to change a source via the end-user UI in other words, via a slicer option? I'd like to select a particular file source that way ie without changing a parameter setting in the parameter editor, or simulation.
Informative and entertaining video. I have issues with a report where the Change Data source button is greyed out. There is a blog post out there that shows how to fix it but it does not work in my case. So I had to manually update all the M queries to make the Source queries more generic and have them configured with parameters.
That's a great trick. In fact, if you use this alongside release pipelines, you can actually control the environments your PBIX model connects too. Now that is 🤯
This is also incredibly amazing for data source migration projects, exactly what i need. Like the video name says this is really amazing tip. Thanks a million GIC :)
Hi Patrick! I have a very interesting task that I couldn't solve yet! You talk about changing data source in different ways but what about the following: I have a data source from the SharePoint folder and a report with many tables in this SP folder. I want to change the data source from SharePoint to a desktop folder that contains the same tables with the same names, sheets and etc. Do you have any ideas on how to do it? Thanks!
We've been using Data Source Settings for some time, especially useful to spot if someone has several connections to the same source but they have mixed case for the connection string (causes a failure in refreshes in PBIRS, until recently).
If you have multiple queries which share the same source is it best practise to create a master query which brings the data in, process any common transformations you need to do for all of the queries then reference that query for each of the sub queries? I've always assumed it's more efficient to only bring the data in once?
Best way I found was to have one source pull query that is then referenced in the other queries. This means that you only have to change the one source query and all the associated sub-queries auto update.
Thanks! Will this work for ONLINE FILES? Maybe I missed something. From OneDrive online, I downloaded to C: drive and cleaned some Excel files. I'd like to change the source back to OneDrive.
Hello Genius, I never miss your any of the videos and you are absolutely amazing in this. Well, i have one query if you could guide me here, - Let's say i have load query from folder and i have multiple files available in that folder belongs to every month sales and let's say i have built one report and loaded in excel using power query. - Now after deleting a few files from folder, how could i keep old file data in excel where i have loaded the query besides that file get removed from folder? - as we know after refracing of query it would load fresh data only in model. basically, i want to preserve old data also in my excel where i have load the query. Thanking you in advance.
Fantastic video, delivered in an entertaining style. Thanks so much! I presume this would work if one wanted to link to a Google sheet instead of an Excel file?
Thanks Patrick for the tip . That is amazing , can I ask quick question on this if you want to import same table from many databases on a Sql server. E.g table abc present in db1 and db2,db3 ... dbn . The structure of the table is same . Is there a better way to do or you have to manually create datasourse for each connection . Thanks
Hi, if you want to change data source for existing reports from azure blob csv to aws rds postgresql materialized views, what process we should follow? And materilized views should have same name as table name which want to replace? And same number, name of columns ?
Your a great host Patrick. Since 2017, I've never seen Excel files sourced from personal laptop C drives in a professional capacity. Only Excel stored on SharePoint Online (or OneDrive at worst). Will this functionally work for switching to a SharePoint stored Excel source either with the SharePoint connector or Web connecter (removing the ?web=1) ?
Hi Patrick, thank you for this kinda content, my question is, what if am working with API endpoints as data sources that are secured using bearer tokens and the base Url changes (local and production) endpoints, this approach is not cutting it. Is there a work around?
I was doing this having a “data_source_query” and disable the load to the model. Then I reference it to the others queries so I only change the source to that data_source_query. I thought it performs better because it only create the connection to the source once and then transform the same query, but I’m not sure whether this way (the one in the video) would perform better. What do you think?
Great Sir, is there any way for the visualization window to display this parameter and the user being able to change the database connection from the visual window without coming into power query window?
I'm looking for a way to add input fields for the user to be able enter a date range and then clicking a button to pass it to a query and apply them as the filters to run the query. I did this in Hyperion Studio on the Dashboard for the user and it works very well. Is this possible in Power BI ?
Have always parameterized the data sources to make deployment easier. Saves a whole bunch of headaches ahead of time. Awesome vid tho. Thanks for sharing.
Thanks for this video Question. This work just for 1 PBI report. What can I do having several PBI reports? All PBI reports are connected to the same data source/Server location and I need to change it to a new Server location. Is there a way to change all data source across all PBI reports rather than open and do it one by one? Thanks
Great video, as always! Thanks... I wanted to parameterize the workspaceid for sourcing a Dataflow, but in Data Source setting, the 'Change source...' button is disabled. Is it the same for you? Ii find I'm using Dataflows more often and it would be nice to have the functionality when migrating in the dev pipeline.
Great trick but has some limitations. For example if you have already parametrized data source via advanced editor(because some data sources doesn't support parameters via UI) it won't allow you to change it via Data source settings
Hi Patrick, I have a situation where I would like to change the "start" and "end" dates for the source query that gets data from a sql server database table by simply updating the "start" and "end" dates in a cell directly in the spreadsheet where the output will go. I have tried multiple ways but am yet to strike the magic formula. I think that would make a great video.
Q: Will this work if my source file has a different number of tabs depending on month? My file contains tabs that represent invoices and every month I have different number of invoices depending on sales.
This works great as long as the input type remains te same. Any idea what to do if I want to replace exactly the same table from a Synapse input to a Azure Databricks Catalog input? (in that case the function needs to change as well, ie from Source = Sql.Database() to Source = Databricks.Catalogs(). Any thoughts on how to prevent manually going through all my queries?
Pretty cool man, I would have (and have in the past) just gone into advanced editor and copy/pasted. Great reminder to use Data Sources to change it and even greater tip to show that parameters can be an option in that pulldown!!
Hi, amazing video. What I was doing before watching this video is:
let's say that I have some data in excel file on sharepoint, so I'm adding the SharePoint source to the point where I can see all my files needed for the report, I usually call this query "SP source" and I leave it there. Then create next queries by referencing this "SP source" query. That way if I need to change the source, I just need to change this "SP source query".
I di the same
I watched this two weeks too late! I just manually updated everything in one of my reports from XLSX to Sharepoint and this would have saved the day! From now on I'm watching your videos as the are published. Can't miss out on any more of these goodies!
I spent 3 hours today doing it the manual way and as I’m going to bed, I figured I’d scroll TH-cam. Lo and behold, I see this video in my recommendations. As much I wanna cry right now, I’m super excited to try this tomorrow with the remaining queries! Thanks Patrick
Patrick has the best PBI content on TH-cam...not even a close second. Great semi technical videos recently. Thank you!
Check Bas's "How to Power BI" and re-evaluate your statement... xD
Agree@@PhilcoCup Bas is very good too.
+1 more reason you guys are my favorite for learning Power BI. I can't tell you how many times this year alone that you all have taken my Power BI skills to the next level and impressed my boss.
Great video... I always use params to point to data sources, that way you can move from dev to uat to production with no pain... Set up the params as a list to make it super smooth.
oh my days! well, now I will be teaching all my internal customers this. Thanks!
This is a good idea
Bro, i have data base connection from mongodb odbc. how to change data source to azure sql server?
I'm a little upset I didn't stumble upon this a month ago. We just went through a server migration and manually updated each connection string with a parameter.... for every single connection in every single report. !!!!!! This would have saved some time! I immediately shared with my team and my client for future reference after I just watched this. Thanks so much!
I like how this guy defines his problems in a very personal way ("I faced THIS problem" etc), it really brings the solution to life
Wow. All this time I've been using parameters and never knew about this simple switch through change data source settings. Admittedly I only go there if there's a credential issue with my source. Thank you for sharing!!!
im been manually changing my source and finally found your video. this method is super efficient and it really save save my time, like a lot. this is insane. thanks you so much
How excited you are to teach us ! It reflects on your face !
You’re awesome Patrick! Thanks for the tip.
I just always set up a “connection only” query to external data source to be reused.
So maybe it’s “sqlconnection” as the query. Then every other one that needs that source is referenced off it (Source = sqlconnection).
If the source changes, I only make 1 change to “sqlconnection” and boom, same result. This change can also be achieved in the data source settings as you showed, just no parameters.
Not everybody knows parameters, so I find this pattern easier to understand. BUT I still really love this video and concepts you’re teaching. Keep it up!
I'm doing it with a custom function that reads a value out of an Excel named range. That advantage (or disadvantage depending on your perspective) is that the user sees the connection string in a range in Excel and can change it without knowing anything about PQ. However, I'm definitely going to use this (I had no idea it existed).
Can you please elaborate? In addition to changing the data base name I would also like to change the sql table name that is used in the query. Table name has date that changes each month
I don't want to get to overexcited but this works for dtaflows with a bit more of a workaround. I just created workspaceID and dataflowID parameters. and added my IDs for both. then added this into each query just the once. it looks like its working and now I can just change the parameters to my production dataflows rather than have to go through every query. thankyou so much
Great video - will this work in situations where the location is tied to an identity? For example if my filepath is: C:\users\myuserid, is there a way to ensure that someone else can jump in and refresh? Would I need to do something else, like a named cell that feeds into the parameter name where the user inputs their userid before refreshing?
This is ideal for switching between test dbs in sql and live ones!
That's right
That’s exactly where my mind went! Dev/test/prod/preprod/production, if you’ve got multiple data sources, that’s going to save a lot of time. Pity it’s fairly well hidden! Well done, Patrick!
Up to now I have been creating a query for each source, then referencing that query whenever I use that source, so changing the “source query” changes all the others.
Using Parameters looks like it’s much cleaner.
I've changed Data sources this way, but didn't realize it would work for multiple! Thanks for sharing.
Fantastic Patrick, and once again your enthusiasm is awesome! Keep up the efficient and good work!
It blew my mind too! You saved me hours of work!! Thank you so much!!
i knew it and i was looking for something else but i watched it till end i gotta say, love your energy man!
Nothing like discovering something as cool as this. I share your excitement. And I will totally take advantage of this.
Update: just tried it and it works exactly as presented. This solves so many issues! Thanks!!!
2nd Update. It works in Excel Power Query too.
I used this trick again today. Such a great tip!
Awesome video. I'm already applying it @ work. Plus, dude your positive attitude shines through. Immediate subscription.
It is like changing an excel data source in Excel PowerPivot data model. There you can have several references to the same file but under existing connections it is easy to change. Thanks for the tip!
Great tip Patrick. Thanks for sharing! I've learned this in Power Query for Excel and used it also in PBI since!
Q: is there a way to change a source via the end-user UI in other words, via a slicer option? I'd like to select a particular file source that way ie without changing a parameter setting in the parameter editor, or simulation.
Great tip! Love the excitement you show in presenting this!
I can use this right now. Great video! Love the enthusiasm
Mate this is the coolest video I've seen in years! Thank you so much for that tip braaaa... woop!
Informative and entertaining video. I have issues with a report where the Change Data source button is greyed out. There is a blog post out there that shows how to fix it but it does not work in my case. So I had to manually update all the M queries to make the Source queries more generic and have them configured with parameters.
That is a life saver video! Thank you Patrick.
That's a great trick. In fact, if you use this alongside release pipelines, you can actually control the environments your PBIX model connects too. Now that is 🤯
Seems nothing is impossible for you in Power BI. Pure genius. I like your short videos full of information.
Absolutely mind blowing !! Thank you Patrick for sharing this great trick !
This is also incredibly amazing for data source migration projects, exactly what i need. Like the video name says this is really amazing tip. Thanks a million GIC :)
Excellent trick Patrick. Many Thanks for sharing the knowledge...!!!
Hi Patrick! I have a very interesting task that I couldn't solve yet! You talk about changing data source in different ways but what about the following:
I have a data source from the SharePoint folder and a report with many tables in this SP folder. I want to change the data source from SharePoint to a desktop folder that contains the same tables with the same names, sheets and etc.
Do you have any ideas on how to do it?
Thanks!
Work smarter not harder. :) This is going to save me so much time. Thanks a bunch! Awesome tip.
We've been using Data Source Settings for some time, especially useful to spot if someone has several connections to the same source but they have mixed case for the connection string (causes a failure in refreshes in PBIRS, until recently).
Awesome trick thanks for sharing.. this will be very helpful with multiple tables needing change in data source
If you have multiple queries which share the same source is it best practise to create a master query which brings the data in, process any common transformations you need to do for all of the queries then reference that query for each of the sub queries? I've always assumed it's more efficient to only bring the data in once?
This was a timely video. Thank you for the content.
This awesome -- just what I was looking for and needed. Also, I knew there had to be a better easier way, Thank you for sharing!
Best way I found was to have one source pull query that is then referenced in the other queries. This means that you only have to change the one source query and all the associated sub-queries auto update.
Thanks! Will this work for ONLINE FILES? Maybe I missed something.
From OneDrive online, I downloaded to C: drive and cleaned some Excel files. I'd like to change the source back to OneDrive.
Infectious energy - love it (and the content)! Keep up the good work guys!
Hello Genius,
I never miss your any of the videos and you are absolutely amazing in this.
Well, i have one query if you could guide me here,
- Let's say i have load query from folder and i have multiple files available in that folder belongs to every month sales and let's say i have built one report and loaded in excel using power query.
- Now after deleting a few files from folder, how could i keep old file data in excel where i have loaded the query besides that file get removed from folder?
- as we know after refracing of query it would load fresh data only in model.
basically, i want to preserve old data also in my excel where i have load the query.
Thanking you in advance.
Fantastic video, delivered in an entertaining style. Thanks so much! I presume this would work if one wanted to link to a Google sheet instead of an Excel file?
Very nice - thank you Patrick, and team.
Thanks Patrick for the tip . That is amazing , can I ask quick question on this if you want to import same table from many databases on a Sql server. E.g table abc present in db1 and db2,db3 ... dbn . The structure of the table is same . Is there a better way to do or you have to manually create datasourse for each connection .
Thanks
Hi Abhishek. Did you find the way to do it?
Great tip and very helpful.... thank you very much for this video
Hi, if you want to change data source for existing reports from azure blob csv to aws rds postgresql materialized views, what process we should follow? And materilized views should have same name as table name which want to replace? And same number, name of columns ?
This is incredibly helpful! How would this work for switching to a web file? (Other Sources > Web)
That's going to save me a bunch of time! Thanks!
Your a great host Patrick. Since 2017, I've never seen Excel files sourced from personal laptop C drives in a professional capacity. Only Excel stored on SharePoint Online (or OneDrive at worst).
Will this functionally work for switching to a SharePoint stored Excel source either with the SharePoint connector or Web connecter (removing the ?web=1) ?
Hi Patrick, thank you for this kinda content, my question is, what if am working with API endpoints as data sources that are secured using bearer tokens and the base Url changes (local and production) endpoints, this approach is not cutting it. Is there a work around?
I was doing this having a “data_source_query” and disable the load to the model. Then I reference it to the others queries so I only change the source to that data_source_query. I thought it performs better because it only create the connection to the source once and then transform the same query, but I’m not sure whether this way (the one in the video) would perform better. What do you think?
Great Sir, is there any way for the visualization window to display this parameter and the user being able to change the database connection from the visual window without coming into power query window?
Great trick, really helpful, specially for beginners like me. thanks
Very good tip. Thanks Patrick.
Hey Pat, maaaaan this is bananas. Really cool staff
I'm looking for a way to add input fields for the user to be able enter a date range and then clicking a button to pass it to a query and apply them as the filters to run the query. I did this in Hyperion Studio on the Dashboard for the user and it works very well. Is this possible in Power BI ?
can we do this in a dataflow?
Thank youuuuuu! always helpful, always entertaining.
Super awesome! I have to update a Sharepoint files everytime our FY changes and this will be a HUGE help!
This is great, from now on I will use this technique for my future power bi report designing
This is legitimately one of the best PQ tips on the internet.
Have always parameterized the data sources to make deployment easier. Saves a whole bunch of headaches ahead of time. Awesome vid tho. Thanks for sharing.
dealing with this exact issue this morning. Perfect timing!
Thanks ! Always usefull ! We did a similar job for switching from Development to Quality and Production System (SAP source).
Thanks for this video
Question. This work just for 1 PBI report. What can I do having several PBI reports?
All PBI reports are connected to the same data source/Server location and I need to change it to a new Server location. Is there a way to change all data source across all PBI reports rather than open and do it one by one?
Thanks
Great video, as always! Thanks...
I wanted to parameterize the workspaceid for sourcing a Dataflow, but in Data Source setting, the 'Change source...' button is disabled. Is it the same for you?
Ii find I'm using Dataflows more often and it would be nice to have the functionality when migrating in the dev pipeline.
Great trick but has some limitations. For example if you have already parametrized data source via advanced editor(because some data sources doesn't support parameters via UI) it won't allow you to change it via Data source settings
Patrick! Thank you so much for this awesome!! bananas!! crazy tip. My Friday has ended well!
Amazing. Saves so much work!
Amazing! Will this work if your data sources if a couple of .csv in a folder? And I want to change the source from file A to file B?
Thanks Patrick for such amazing tip!
Liked it even before I saw it. This is an amazing tip.
You’re a life saver!!! Thank you!
I found this very useful. Saved a lot of stress
This was amazing trick.. thank you Patrick!
Patrick you are genius!!! thanks
Totally INSANE AMAZING!! Thank you!!
Hi Patrick, I have a situation where I would like to change the "start" and "end" dates for the source query that gets data from a sql server database table by simply updating the "start" and "end" dates in a cell directly in the spreadsheet where the output will go. I have tried multiple ways but am yet to strike the magic formula. I think that would make a great video.
Does casing have to match exactly or will it treat case changes the same?
Q: Will this work if my source file has a different number of tabs depending on month? My file contains tabs that represent invoices and every month I have different number of invoices depending on sales.
This works great as long as the input type remains te same. Any idea what to do if I want to replace exactly the same table from a Synapse input to a Azure Databricks Catalog input? (in that case the function needs to change as well, ie from Source = Sql.Database() to Source = Databricks.Catalogs(). Any thoughts on how to prevent manually going through all my queries?
This is fantastic, nice one!
Just found you! Great teaching and tips! Subscribing!!
Is there any possibility to use this parameters options to have different tables from databricks and change it dinamically in power bi desktop?
Great tip, Patrick, thank you. This can not be done in Dataflows, right?
This channel is just amazing!!!!!!!!
Pretty cool man, I would have (and have in the past) just gone into advanced editor and copy/pasted. Great reminder to use Data Sources to change it and even greater tip to show that parameters can be an option in that pulldown!!
Does it make the changes in the visuals too without disturbing the existing design?
Great video! Im gonna try this now :) Thank you Patrick!
Great Tip. Had to subscribe!
This is a GREAT tip. Thanks so much.
Very nice trick 👏👍 I love it
Great tip!. This helps us to switch between environments easily :)