Change Excel Source in Power BI to OneDrive for Business No Gateway Needed

If you used an excel file in a local source (or even OneDrive for Business, but sourced locally), you need to do one extra step to make your query refreshable without the need to the gateway. Here in this video, I’ll explain an easy way to do that change.
Step by step guide is also explained here in my blog article:
radacad.com/change-excel-sour...

[@masgitz]

Now this is what I call just in time content.i was struggling with this last night

[@erumalibhatti1218]

Thank you so much, you saved my day i was trying to change the source from sharepoint but was unsure of the process.you are a gem

[@govindaaryal10]

Thank you!! I was struggling and googling since long time and I found the solution here. 😊😊

[@MarioBejaranoM]

Great source of info! You save me trouble going trough gateways! Thanks

[@diekmaas7249]

Seriously this was the only video I could find that helped me. Thumbs up bro.

[@diekmaas7249]

One remaining question: When you connect "organizational"". What happens to the sharepoint connection when I change my password of my 365 account?

[@ankitojha6450]

Thanks alot - was searching for this solution from a long time.

[@anac.benavidesb.212]

Thank you so much! This was the only video I could find that helped me

[@DeepakrajSolanki]

I am still not able to resolve the issue. I get the following error. Expression.Error: Access to the resource is forbidden.

[@hilmanagung]

Super helpful - thanks Reza!

[@RoyAtVjenne]

Great video ! This helps a lot! Thanks

[@jakedelarosa2790]

I finally found it. This works for me. Thanks!

[@mytdexter]

Thank you so much. I've tested this and it's working.🙂

[@brockyoutube]

Brilliant! Great explanation

[@pablosaizortuno4740]

Impresive! very good explanation!

[@RADACAD]

Glad it helped!

[@omnis73]

Excellent! Thank you so much.

[@datasciencewithugur]

Great video ! Thank you so much !

[@RADACAD]

You are welcome!

[@ahmadelmasri9702]

Perfect explanation, thank you!

[@RADACAD]

Glad you enjoyed it!

[@naomiprofesorsky5747]

Thank you this was very helpful

[@PedroCabraldaCamara]

awesome!! Thank you so much Reza

[@MrAliviva7]

Helped me a lot.thanks

[@MsAnnyMa]

Thank you so much :) It works so great!

[@RADACAD]

Great to hear!

[@MagnusAasen]

You saved my week!

[@mchandrasekhar05]

Really helpful!

[@reewindfy]

Hi. Thanks for the videos. I am getting the following error when i change the excel file source to one drive for business. (Expression.Error: Access to the resource is forbidden.). Is this something which can be solved? Thanks

[@agileHRanalytics]

Thanks for the great tip!

[@RADACAD]

😊👊

[@victorcardoso1924]

excellent content!

[@isidrocentelles1844]

If you get the data from an Excel on One drive it's better if you linked directly from the full URL document deleting "?web=1" from the left and get data Web. To get the full URL you must click just under the document title (OneDrive - CG)

[@RADACAD]

Great to know. Thanks 😊👊

[@elcheo66]

Excellent, thanks.

[@mohammedadnan2147]

You are awesome... Thank you...

[@gondebas]

Hi Reza, what if we have various kinds of data sources, for example one Excel file on OneDrive and a table from SQL Server. Can we set up automatic refresh of OneDrive source without the gateway?

[@sandeepg4705]

Thank you

[@avanishpank7572]

How do I manage the gateway connection when earlier i was using on-premise data and now I want to move that data (folder/ file) to one drive? I changed the path and the source file now shows one drive but im still being asked to install a gateway connection and unable to edit data source credentials.

[@mohammedimran4257]

Hi, I'm facing problem with schedule refresh. Files (.xlsx)are saved on Local folder so using Gateways and error is Mashup data error and also it say External table is not in expected format. Everytime i check for detailed error message it will show another table and error msg is same. Plz help

[@The-Spark-Within]

you saved me so much of hassle

[@learnData-oo5yk]

How to do the same for onedrive personal

[@desenterrados2022]

Thanks a lot! Does this apply to OneDrive (personal)?

[@RADACAD]

I don't think so. I ran a quick test. It seems OneDrive personal is using a different authentication mechanism that doesn't allow that. At least for my test it didn't work.

[@desenterrados2022]

@@RADACAD Thanks a lot!!!!

[@PallaviChauhan91]

What if, after changing the data source of desktop file, instead of publishing it to power Bi - we go in power bi service & choose - "My workspace / + New / Upload a File / One Drive Business" and pick the same pbix file that is now also synced from our desktop to our one drive business? Would it refresh the file in service - automatically - everytime we hit refresh in pbix file offline? I saw something like that on guy in a cube. What do you think? (thanks for the trick to update data source 👍)

[@RADACAD]

although putting your PBIX file on OneDrive for Business will help with hourly refresh automatically. however, still the user need to refresh the PBIX file for it.

[@SanishJohn]

Will I be able to email in an updated file to OneDrive Biz and use this solution? Great video btw!

[@RADACAD]

Not sure what do you mean by the email. If any new file appears in the folder, you will get the data of that in the next refresh, if this is what you mean.

[@SanishJohn]

@@RADACAD Sorry, Let me clarify. Box has a feature where you can email files into a folder (box can generate email IDs by folders) which can be used to email in updated versions (this may be a more OneDrive feature questions rather than 'Power BI to OneDrive' question). I did find a work around using 'Flow' by using a template (Save Office 365 email attachments to OneDrive for Business). Thanks

[@shehabxcode]

Can I do the same but using onedrive not onedrive for business

[@albus_subla]

How safe is this solution? Maybe someone could "guess" the link and get access to the file? Thanks!

[@cezaryczajka9198]

Great advice I have a question - how to use a similar solution to retrieve data from a folder OneDrive?

[@RADACAD]

Good question. In that case, you can use SharePoint.Files to get files inside a OneDrive folder because behind the scene it is a SharePoint folder. I might publish a video about that too. the method is: SharePoint.Files

[@cezaryczajka9198]

Thank you for your response. Please post advice on this topic. Regards

[@RADACAD]

@@cezaryczajka9198 that video is now published

[@cezaryczajka9198]

Thank you very much. I have the following problem - when I change the data source to my SharePoint.Contents I get an empty table. I changed my organizational credentials and non-setbacks still nothing.

[@RADACAD]

@@cezaryczajka9198 have you tried SharePoint.Files too? What is the url you are using?

[@prakash4190]

I know if it is sourced from onedrive location it should refresh every 1 hour. Will this solution refresh automatically every one hour?

[@RADACAD]

unfortunately not. If you want the hourly refresh to happen, you should build the Power BI dataset in the website (service), that creates a different type of dataset with the OneDrive refresh hourly. This method, would be like a normal Power BI dataset with the limitation of your Power BI account license.

[@prakash4190]

@@RADACAD Thanks! Much appreciated!

[@stoneage8810]

if its not power BI, but power pivot, would this approach still work ?

[@RADACAD]

You won't need gateway for power pivot anyways. Because there is no scheduled refresh for excel

[@farmanalishah8836]

Thanks for the useful advice. I am getting this error "Expression.Error: Access to the resource is forbidden." Can you please help?

[@RADACAD]

what url are you using? and did you logged in with the same account that has access to that url?

[@farmanalishah8836]

@@RADACAD Thanks Reza after logged in now successfully connected to the source.. Thanks👍

[@gondebas]

Shortcut: 3:15 Check your data sources 3:53 Get the cloud path to your OneDrive file 5:15 Change the local path in the M editor to the cloud path (the gem of this movie) 6:00 Change File.Contents to Web.Contents 6:56 Open report, ellipses, "View related" ("See related content") 6:59 Click on "Schedule refresh" icon

[@RADACAD]

Thanks for the shortcut!

[@JudyQuintero]

Does it still work if the excel file is located in a private folder in your organization? my options are limited :(

[@RADACAD]

If you have access to that folder in OneDrive for Business then yes, you can access it

[@JudyQuintero]

​@@RADACAD it worked, thank you!! :)

[@libinvarghese1]

How to do the same when the data source is a folder in One drive?

[@soufianattar4554]

estez !!!!!