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
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
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)
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
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.
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.
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?
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.
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 👍)
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.
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.
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
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.
@@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
Now this is what I call just in time content.i was struggling with this last night
Thanks alot - was searching for this solution from a long time.
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
Seriously this was the only video I could find that helped me. Thumbs up bro.
One remaining question: When you connect "organizational"". What happens to the sharepoint connection when I change my password of my 365 account?
Great source of info! You save me trouble going trough gateways! Thanks
Impresive! very good explanation!
Glad it helped!
I finally found it. This works for me. Thanks!
Thank you so much! This was the only video I could find that helped me
Thank you!! I was struggling and googling since long time and I found the solution here. 😊😊
I am still not able to resolve the issue. I get the following error.
Expression.Error: Access to the resource is forbidden.
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
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
Thanks for the shortcut!
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)
Great to know. Thanks 😊👊
Perfect explanation, thank you!
Glad you enjoyed it!
Thank you so much. I've tested this and it's working.🙂
Great video ! Thank you so much !
You are welcome!
You saved my week!
Thank you so much :) It works so great!
Great to hear!
Thank you this was very helpful
Great advice
I have a question - how to use a similar solution to retrieve data from a folder OneDrive?
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
Thank you for your response. Please post advice on this topic. Regards
@@cezaryczajka9198 that video is now published
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.
@@cezaryczajka9198 have you tried SharePoint.Files too? What is the url you are using?
Brilliant! Great explanation
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.
Excellent! Thank you so much.
Great video ! This helps a lot! Thanks
Super helpful - thanks Reza!
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?
I know if it is sourced from onedrive location it should refresh every 1 hour. Will this solution refresh automatically every one hour?
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.
@@RADACAD Thanks! Much appreciated!
Helped me a lot.thanks
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 👍)
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.
Thanks a lot! Does this apply to OneDrive (personal)?
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.
@@RADACAD Thanks a lot!!!!
you saved me so much of hassle
Really helpful!
excellent content!
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
awesome!! Thank you so much Reza
Thanks for the great tip!
😊👊
Can I do the same but using onedrive not onedrive for business
Will I be able to email in an updated file to OneDrive Biz and use this solution? Great video btw!
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.
@@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
Does it still work if the excel file is located in a private folder in your organization? my options are limited :(
If you have access to that folder in OneDrive for Business then yes, you can access it
@@RADACAD it worked, thank you!! :)
How to do the same for onedrive personal
Excellent, thanks.
Thanks for the useful advice. I am getting this error "Expression.Error: Access to the resource is forbidden." Can you please help?
what url are you using? and did you logged in with the same account that has access to that url?
@@RADACAD Thanks Reza after logged in now successfully connected to the source.. Thanks👍
How to do the same when the data source is a folder in One drive?
if its not power BI, but power pivot, would this approach still work ?
You won't need gateway for power pivot anyways. Because there is no scheduled refresh for excel
You are awesome... Thank you...
How safe is this solution? Maybe someone could "guess" the link and get access to the file? Thanks!
Thank you
estez !!!!!