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!
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 spent 3 hours today doing it the manual way and as I’m going to bed, I figured I’d scroll UA-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
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!
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 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
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 🤯
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
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.
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 :)
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!
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.
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.
But Patrick, if have you have for example and Excel/CSV file feading a few queries and change the path only one time in "Data source settings", wont that give you the same result? I believe it will. Otherwise, it's a nice technique, but trying to figure out the benefts to store the path in a parameter instead of "Data source settings" directly. Thanks again for the nice video!
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!
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?
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.
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.
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) ?
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).
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
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.
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!!
Using a parameter for sqlserver / sqlDB is great as you can update the parameters in the PBI service as well once you have published the pbix without needing to go into PQ to change the values
@@antonycatella5901 Hi Antony, hope you can help... We have created the parameter allowing us to switch between two SQL servers, this works great in Desktop, but when we upload to the service, we have no option to change the parameter - it is fixed at the last value when we uploaded the PBIX? You say you are able to so this? Thanks
@@marklund7498 Not really sure why you cannot do it. When I publish a pbix to the service I am able to change the server name in the service but you don't get the benefit of the 'dropdown list' that you can use in PBI desktop. You have to manually write in the name of the 'new' server in the Parameters section under Manage Gateways.
@@antonycatella5901 Thanks for the reply! When I expand Parameters in the Dataset settings page, it is definitely locked and not allowing me to overtype the value. Both servers are setup in the Gateway, but again the Gateway is pointing to the server which was set in the PBIX when uploaded and no option to change. Frustrating as we are migrating from an On-Prem SQL to Azure SQL (with the same named SQL database) and it would be great to be able to easily switch servers without having to re-uploads all our reports on go-live day.
Hi @Antony, looks like I have found the issue. When setting up my parameter I left the type as 'Any'. I changed this to Text and I am now able to modify the value in the service
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
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?
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
I just run across a project which had the source on a parameter for a SharePoint root folder, and i thought nice! Only to find that the following step filters folder using the whole absolute URL making the parametritzation useless... 😅
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 like how this guy defines his problems in a very personal way ("I faced THIS problem" etc), it really brings the solution to life
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 spent 3 hours today doing it the manual way and as I’m going to bed, I figured I’d scroll UA-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.
Patrick has the best PBI content on UA-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@@juanm555 Bas is very good too.
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!
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
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?
You’re awesome Patrick! Thanks for the tip.
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!
I've changed Data sources this way, but didn't realize it would work for multiple! Thanks for sharing.
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 knew it and i was looking for something else but i watched it till end i gotta say, love your energy man!
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
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!!
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 legitimately one of the best PQ tips on the internet.
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!
Mate this is the coolest video I've seen in years! Thank you so much for that tip braaaa... woop!
Seems nothing is impossible for you in Power BI. Pure genius. I like your short videos full of information.
Awesome video. I'm already applying it @ work. Plus, dude your positive attitude shines through. Immediate subscription.
Great tip Patrick. Thanks for sharing! I've learned this in Power Query for Excel and used it also in PBI since!
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
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.
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 :)
This is great, from now on I will use this technique for my future power bi report designing
Work smarter not harder. :) This is going to save me so much time. Thanks a bunch! Awesome tip.
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!
I can use this right now. Great video! Love the enthusiasm
That's going to save me a bunch of time! Thanks!
Great tip! Love the excitement you show in presenting this!
That is a life saver video! Thank you Patrick.
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.
This was a timely video. Thank you for the content.
Absolutely mind blowing !! Thank you Patrick for sharing this great trick !
Great trick, really helpful, specially for beginners like me. thanks
I found this very useful. Saved a lot of stress
Awesome trick thanks for sharing.. this will be very helpful with multiple tables needing change in data source
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!
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.
This is SOOOO amazing! Thanks for sharing this great tip.
Excellent trick Patrick. Many Thanks for sharing the knowledge...!!!
Super awesome! I have to update a Sharepoint files everytime our FY changes and this will be a HUGE help!
Infectious energy - love it (and the content)! Keep up the good work guys!
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.
Very nice - thank you Patrick, and team.
Patrick you are genius!!! thanks
You’re a life saver!!! Thank you!
But Patrick, if have you have for example and Excel/CSV file feading a few queries and change the path only one time in "Data source settings", wont that give you the same result? I believe it will.
Otherwise, it's a nice technique, but trying to figure out the benefts to store the path in a parameter instead of "Data source settings" directly.
Thanks again for the nice video!
yeah your idea does the trick.. I was really confused while watching this video on why he doesnt do it like this
Very good tip. Thanks Patrick.
Mindblown lifesawing ayee
Amazing. Saves so much work!
This is incredibly helpful! How would this work for switching to a web file? (Other Sources > Web)
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!
Great tip and very helpful.... thank you very much for this video
Thank youuuuuu! always helpful, always entertaining.
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?
Liked it even before I saw it. This is an amazing tip.
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.
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.
dealing with this exact issue this morning. Perfect timing!
Totally INSANE AMAZING!! Thank you!!
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) ?
Thanks ! Always usefull ! We did a similar job for switching from Development to Quality and Production System (SAP source).
I had this issue some days ago but didn't known you already have it in your channel 😂
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).
Hey Pat, maaaaan this is bananas. Really cool staff
Freak'n AMAZING! Absolutely solves the same issue I have. Very elegant solution! A+
This was amazing trick.. thank you Patrick!
I'm this excited too when I'm able to crack a powerbi issue😂 Thanks Patrick
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
Very nice trick 👏👍 I love it
Tanks a million Patrick!
Patrick! Thank you so much for this awesome!! bananas!! crazy tip. My Friday has ended well!
Great tip, Patrick, thank you. This can not be done in Dataflows, right?
Very.Nice.Solution!
Awesome, Thank you Brother..
This is really fantastic!!
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.
I like your energy at the start of the video. Booommmmm
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!!
I knew this. Brilliant tip.
Great Tip. Had to subscribe!
This is fantastic, nice one!
Love the timing of these videos. Been switching a data source from testing to production and will have to give this a try
Using a parameter for sqlserver / sqlDB is great as you can update the parameters in the PBI service as well once you have published the pbix without needing to go into PQ to change the values
@@antonycatella5901 Hi Antony, hope you can help... We have created the parameter allowing us to switch between two SQL servers, this works great in Desktop, but when we upload to the service, we have no option to change the parameter - it is fixed at the last value when we uploaded the PBIX? You say you are able to so this? Thanks
@@marklund7498 Not really sure why you cannot do it. When I publish a pbix to the service I am able to change the server name in the service but you don't get the benefit of the 'dropdown list' that you can use in PBI desktop. You have to manually write in the name of the 'new' server in the Parameters section under Manage Gateways.
@@antonycatella5901 Thanks for the reply! When I expand Parameters in the Dataset settings page, it is definitely locked and not allowing me to overtype the value. Both servers are setup in the Gateway, but again the Gateway is pointing to the server which was set in the PBIX when uploaded and no option to change. Frustrating as we are migrating from an On-Prem SQL to Azure SQL (with the same named SQL database) and it would be great to be able to easily switch servers without having to re-uploads all our reports on go-live day.
Hi @Antony, looks like I have found the issue. When setting up my parameter I left the type as 'Any'. I changed this to Text and I am now able to modify the value in the service
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
Insane Amazing!! You saved me some hours!
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?
This is awesome, thank you!
Great video! Im gonna try this now :) Thank you Patrick!
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?
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?
I just run across a project which had the source on a parameter for a SharePoint root folder, and i thought nice! Only to find that the following step filters folder using the whole absolute URL making the parametritzation useless... 😅
This is a GREAT tip. Thanks so much.
Great tip!. This helps us to switch between environments easily :)
Patrick!! You are brilliant. You are a nerd. WOW!