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

If you need to change your data source references in Power Query, you are probably doing them individually. We found an INSANE AMAZING tip to make you way more efficient and it works in Power BI and Excel!
📢 Become a member: guyinacu.be/membership
*******************
Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.
🎓 Guy in a Cube courses: guyinacu.be/courses
*******************
LET'S CONNECT!
*******************
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
🛠 Check out my Tools page - guyinacube.com/tools/
#PowerBI #PowerQuery #GuyInACube

[@marshmallow713]

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

[@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.

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

[@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

[@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

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

[@tomduffhues622]

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

[@cristian.angyal]

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

[@DPFierce]

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

[@rebeccaallison468]

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

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

[@driouchemountasir7894]

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

[@miguelg6265]

You’re awesome Patrick! Thanks for the tip.

[@kayespedal10]

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

[@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

[@hi_vishy]

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

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

[@hikaru6969]

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

[@aamoody81]

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

[@juanm555]

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

[@SpojeHoob]

Agree@@juanm555 Bas is very good too.

[@pritivalecha3147]

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

[@sjames1958]

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

[@akshaynm]

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

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

[@stevejkearney]

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

[@Paul___L]

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

[@EdwardoZ]

dealing with this exact issue this morning. Perfect timing!

[@Mivoat]

Very nice - thank you Patrick, and team.

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

[@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

[@manfredwimberger5195]

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

[@enochosaji-okai1845]

That is a life saver video! Thank you Patrick.

[@LatinoTropico]

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

[@notoriousft]

Very good tip. Thanks Patrick.

[@walterwine]

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

[@samuelmanseau6905]

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

[@ConcordHk]

Great video! Im gonna try this now :) Thank you Patrick!

[@ricardodanielanaya7028]

I had this issue some days ago but didn't known you already have it in your channel 😂 Thanks!

[@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 🤯

[@email2niranjan]

Always did the change through Advanced settings... Not any more after this video :) thank you

[@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

[@violetnha]

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

[@jameslucas5590]

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

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

[@humayunshahzad9181]

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

[@ezidora]

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

[@adebimpeadelakun8806]

I'm this excited too when I'm able to crack a powerbi issue😂 Thanks Patrick

[@gunjanlalwani1654]

Thanks Patrick for such amazing tip!

[@Bobioe]

I found this very useful. Saved a lot of stress

[@dbszepesi]

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

[@nidhisharma47]

This was amazing trick.. thank you Patrick!

[@NJOYtommy]

Thank youuuuuu! always helpful, always entertaining.

[@InsightfulJourney123]

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

[@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.

[@AmySwayzee]

This is a GREAT tip. Thanks so much.

[@tjtj9413]

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

[@jaimewalter1917]

Freak'n AMAZING! Absolutely solves the same issue I have. Very elegant solution! A+

[@gonzispace]

Muy bueno el tip, muchas gracias.

[@Darr1ken]

Great Tip. Had to subscribe!

[@allthingspowerbiwithadebol3576]

Nice and brilliant. What ive had to do in my case, was to have different parameters against each table cos they came from different databases n environments. But, this is equally good, cos wasn't aware of the options settings to always allow parameters, nice one

[@shepherdmakanza5953]

Hey Pat, maaaaan this is bananas. Really cool staff

[@sainathithabhojana7138]

Awesome, Thank you Brother..

[@nataschalaros4334]

Amazing. Saves so much work!

[@adityap8210]

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

[@stephaniewilson537]

Totally INSANE AMAZING!! Thank you!!

[@chelseafarias6981]

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

[@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.

[@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.

[@emresahindance]

Mindblown lifesawing ayee

[@ashwin147]

Great tip!. This helps us to switch between environments easily :)

[@antoniogarrido3058]

Awesome... Thanks for sharing!!! 👍

[@julieinalaska]

This is awesome, thank you!

[@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

[@AdibYC]

Patrick you are genius!!! thanks

[@aburnett04]

Awesome! thanks for sharing

[@jeffnixon4868]

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.

[@user-kw7ce2mh3j]

This is really fantastic!!

[@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?

[@bastoubabast7618]

Thanks for sharing, awesome !!

[@matthewbrierley88]

This is fantastic, nice one!

[@diegoj9056]

Insane Amazing!! You saved me some hours!

[@at-excel]

This is SOOOO amazing! Thanks for sharing this great tip.

[@madhanrajmoorthy8843]

Lovely :) Thank you. helpful

[@pavelrehak5481]

Love it Patrick! Thanks for this cool trick ;)

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

[@brianspiller9075]

Yooo! Used today. Glad i'm subscribed.

[@RicardoSadaJapp]

AMAZING! Thanks

[@quentinwinisdoerffer421]

Did know about it :D But always nice showing tricks! Thanks

[@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?

[@szita2000]

I knew this. Brilliant tip.

[@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?

[@richisb6607]

Thank you as always!

[@jorisvandeBrande]

I started using parameters for my links. Dang time efficient

[@Nitzfused]

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

[@ssma1368]

Tanks a million Patrick!

[@ajaaskelainen]

Spreading good practice for the win! It is also important to have a staging query, to which the rest of the queries are referring to. It organizes everything neatly under one source in query dependency view! This is also useful if you have multiple data sources. P.S good tip of changing source to a parameter from the settings. The first time I did it, I did it the hard way :)

[@marciobina]

That's an amazing tip!! Is there some way to reach the same result on pbi dataflows?

[@edwincastro3346]

Amazing video now I wont change data source 1x1. Thank you for sharing this great tip.

[@dsapraveen]

Amazing tip Patrick :)

[@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?

[@goodideatia]

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