INSANE AMAZING Power Query tip to quickly change data sources in Power BI and Excel

[@RafaMaculewicz]

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".

[@zacharybaker3602]

I di the same

[@shaunwthompson]

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!

[@aamoody81]

Patrick has the best PBI content on KZbin...not even a close second. Great semi technical videos recently. Thank you!

[@PhilcoCup]

Check Bas's "How to Power BI" and re-evaluate your statement... xD

[@SpojeHoob]

Agree@@PhilcoCup Bas is very good too.

[@Ahmadj03]

I spent 3 hours today doing it the manual way and as I’m going to bed, I figured I’d scroll KZbin. 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

[@fahmiamni]

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

[@bettina.e-k]

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.

[@jimmyd7377]

oh my days! well, now I will be teaching all my internal customers this. Thanks!

[@kstek1208]

This is a good idea

[@usmansharifshaik1187]

Bro, i have data base connection from mongodb odbc. how to change data source to azure sql server?

[@KetakiGadgil_15]

How excited you are to teach us ! It reflects on your face !

[@beyondtriggered4145]

+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.

[@pritivalecha3147]

i knew it and i was looking for something else but i watched it till end i gotta say, love your energy man!

[@kayespedal10]

Awesome video. I'm already applying it @ work. Plus, dude your positive attitude shines through. Immediate subscription.

[@miguelg6265]

You’re awesome Patrick! Thanks for the tip.

[@lorisean06]

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!

[@xxczerxx]

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

[@hikaru6969]

It blew my mind too! You saved me hours of work!! Thank you so much!!

[@enochosaji-okai1845]

That is a life saver video! Thank you Patrick.

[@jameslucas5590]

This was a timely video. Thank you for the content.

[@rebeccaallison468]

I've changed Data sources this way, but didn't realize it would work for multiple! Thanks for sharing.

[@therevenant211]

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?

[@LatinoTropico]

Mate this is the coolest video I've seen in years! Thank you so much for that tip braaaa... woop!

[@MacPaulos]

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!!!

[@shepherdmakanza5953]

Hey Pat, maaaaan this is bananas. Really cool staff

[@tomduffhues622]

Fantastic Patrick, and once again your enthusiasm is awesome! Keep up the efficient and good work!

[@walterwine]

Awesome trick thanks for sharing.. this will be very helpful with multiple tables needing change in data source

[@lewistremonti7827]

This is ideal for switching between test dbs in sql and live ones!

[@raz8657]

That's right

[@waysilentbob6976]

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!

[@kstek1208]

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!

[@ezidora]

Great tip and very helpful.... thank you very much for this video

[@notoriousft]

Very good tip. Thanks Patrick.

[@infinger2006]

That's going to save me a bunch of time! Thanks!

[@NJOYtommy]

Thank youuuuuu! always helpful, always entertaining.

[@akshaynm]

Excellent trick Patrick. Many Thanks for sharing the knowledge...!!!

[@stevejkearney]

I can use this right now. Great video! Love the enthusiasm

[@Mivoat]

Very nice - thank you Patrick, and team.

[@ryanfisher5835]

Nothing like discovering something as cool as this. I share your excitement. And I will totally take advantage of this.

[@ryanfisher5835]

Update: just tried it and it works exactly as presented. This solves so many issues! Thanks!!!

[@ryanfisher5835]

2nd Update. It works in Excel Power Query too.

[@ryanfisher5835]

I used this trick again today. Such a great tip!

[@philippetorres7180]

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.

[@humayunshahzad9181]

Great trick, really helpful, specially for beginners like me. thanks

[@violetnha]

Work smarter not harder. :) This is going to save me so much time. Thanks a bunch! Awesome tip.

[@debbieedwards7267]

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

[@DPFierce]

Great tip! Love the excitement you show in presenting this!

[@InsightfulJourney123]

Just found you! Great teaching and tips! Subscribing!!

[@Bren4000]

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.

[@Darr1ken]

Great Tip. Had to subscribe!

[@AdibYC]

Patrick you are genius!!! thanks

[@nidhisharma47]

This was amazing trick.. thank you Patrick!

[@stephaniewilson537]

Totally INSANE AMAZING!! Thank you!!

[@cristian.angyal]

Great tip Patrick. Thanks for sharing! I've learned this in Power Query for Excel and used it also in PBI since!

[@chelseafarias6981]

You’re a life saver!!! Thank you!

[@matthewbrierley88]

This is fantastic, nice one!

[@Bobioe]

I found this very useful. Saved a lot of stress

[@mikloshimics7789]

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!

[@driouchemountasir7894]

Absolutely mind blowing !! Thank you Patrick for sharing this great trick !

[@johnnymeredith1960]

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).

[@somidixit9673]

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

[@tsvetanp9377]

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!

[@stuartbenjamin1646]

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?

[@kevinlabore1726]

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!

[@walterstevens8676]

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.

[@Paul___L]

Infectious energy - love it (and the content)! Keep up the good work guys!

[@nataschalaros4334]

Amazing. Saves so much work!

[@tjtj9413]

Super awesome! I have to update a Sharepoint files everytime our FY changes and this will be a HUGE help!

[@julieinalaska]

This is awesome, thank you!

[@goodideatia]

This is incredibly helpful! How would this work for switching to a web file? (Other Sources > Web)

[@FredericLEGUEN-Excel]

Very nice trick 👏👍 I love it

[@diegoj9056]

Insane Amazing!! You saved me some hours!

[@hi_vishy]

Seems nothing is impossible for you in Power BI. Pure genius. I like your short videos full of information.

[@onyxj589]

This is really fantastic!!

[@kiwim3p587]

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?

[@user-px8vc2iv9l]

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?

[@saulazcona1985]

Thanks Patrick for such amazing tip!

[@sainathithabhojana7138]

Awesome, Thank you Brother..

[@smartwork4768]

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.

[@marshmallow713]

This is legitimately one of the best PQ tips on the internet.

[@adityap8210]

Liked it even before I saw it. This is an amazing tip.

[@Nitzfused]

This channel is just amazing!!!!!!!!

[@CalvinPuah]

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.

[@erikvanhurck]

Very.Nice.Solution!

[@AmySwayzee]

This is a GREAT tip. Thanks so much.

[@TSmitty360]

Does casing have to match exactly or will it treat case changes the same?

[@gonzispace]

Muy bueno el tip, muchas gracias.

[@samuelmanseau6905]

Thanks ! Always usefull ! We did a similar job for switching from Development to Quality and Production System (SAP source).

[@aburnett04]

Awesome! thanks for sharing

[@giovanyrodriguez8884]

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?

[@judeLondon]

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) ?

[@warriorppr]

Does it make the changes in the visuals too without disturbing the existing design?

[@ssma1368]

Tanks a million Patrick!

[@DelkyFlex]

Great video. I'm interested in how you can do this for SharePoint Folder

[@swapnab4453]

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 ?

[@bastoubabast7618]

Thanks for sharing, awesome !!

[@txreal2]

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.

[@brianspiller9075]

Yooo! Used today. Glad i'm subscribed.

[@abhishekgoelutube]

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

[@RicardoRomoEncino]

Hi Abhishek. Did you find the way to do it?

[@manfredwimberger5195]

Patrick! Thank you so much for this awesome!! bananas!! crazy tip. My Friday has ended well!

[@LuisSilva-bp6sl]

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 ?

[@dsapraveen]

Amazing tip Patrick :)

[@emirtuncer]

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 :)

[@lyonelremond4075]

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 🤯

[@maserati1220]

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?

[@ErniePantuso]

I knew this. Brilliant tip.

[@ShadoWMoRiA]

I like your energy at the start of the video. Booommmmm

[@EdwardoZ]

dealing with this exact issue this morning. Perfect timing!

[@Fernando_Calero]

Great tip, Patrick, thank you. This can not be done in Dataflows, right?

[@PepStreebeck]

Great tip. But catchy opening theme. What is the music? Thanks