Just here to say that I love your explanations Patrick! It's 3:00 AM and I am studying for the PL-300 and your way of explaining made me more engaged. Keep up this channel guys!
You just saved my life. For all the people watching this tutorial now, if you can't find the link in your webbrowser, open the excel sheet in your app and then go to Information and copy path. :)
Hi patrick, have been following your videos for quite sometime now and i like your tricks and tips. in my organization we are trying to connect to Sap bw database from Power bi using sso and have been able to do from power bi desktop but when we try to add the database in gateway we get error and we have been following up with Microsoft from last 4 months on this issue but support guys are not that technical experts. so wanted to check if you have ever tried connecting Sap database to Power Bi and adding it in Gateway. if yes please show us how you did it. I bet this is the biggest requirement for most of the big organization.
@Patrick: You said so many times Power BI is smart but unfortunately is not quite easy :( I know every user, even me, had use Get Data>Excel. Thanks for this video and sharing your knowledge! You rock!
I do it the following way: Exporting SAP-Tables as csv Files in Onedrive Folder at night and syncing with Sharepoint Website Folder. Running PowerBI Reports (Data Source Sharepoint Folder) in the PBI Service with a scheduled refresh every morning. Works perfectly!
@@PedroFernandezRuiz Our customer, who does not use the SAP BW, wrote a small program to export the original data tables automatically. We use 35 SAP Data Tables over most SAP modules with a daily update and additional some Excels in our PowerBI-project. Another customer uses the BEx Analyzer of SAP to export the data. Hope this helps?
@@goristhibaut5817 No, you do not need a gateway. OneDrive is syncing all data from the OneDrive Folder on the local machine to Sharepoint (Cloud). The Connection from PowerBI (Service) to the data is via "Sharepoint Folder", so you do not need a Gateway from PowerBI to a local machine. You only need a Sharepoint Connection to get a scheduled refresh in PowerBI Service. Ok?
Hi thanks for this video I need to know that can put live pivot table from one drive link? If it is can you please make a video on that it will be too helpful
Good explanation... and I smash LIKE... BUT... with google sheets, I don't have to click refresh. The realtime updates are crucial for coordinating complex events.
the video and delivery are awesome. but i have a question here, with the same purpose, is this the "official" method or a workaround? i mean you have to edit the url first in the notepad (?)
cool ...very helpful... what if the connection is already using excel offline, is there a way to convert everything to excel online (onedrive)? so I don't have to do a reset on the dashboard
Thanks for this! I'm doing this with a report im working on. Every now and then I get an error in the refresh when i REPLACE the file, even with same filename, sheet name and columns (its a CSV actually, but same sheetname still). I get errors when I REPLACE, but not when I edit the file and add the new rows manually. Why/how is that? Thanks again!
I know its almost a year late but would it work the same if the excel file lives on a SharePoint and not on a OneDrive? Thanks for all the amazing videos!
Yes, if you go to Power BI service and click "Get data" and choose onedrive, then create a report on that dataset. Directly in the PBI service, or if you want to do it in Power BI desktop using Power BI datasets source. The updates from the excel will be instantaneous.
Hi Patrick, great job. I have been struggling long. i have a an app which uses 2010 access mdb. is there any method where i can access that in power bi for reports , since it says that its in previous version
Hi Patrick, I am finally trying this method and I think it’s great. However I save my Power BI files on OneDrive and I’ve been getting errors, mostly about the excel file being corrupt. I’ve read somewhere that it could be caused by sync issues. Any ideas?
If you don’t have a data gateway, but have office 365 / one drive, can you save an excel file with power queries which update data then save that file to one drive ? How would you upload it so it’s a refreshable file ? Or can you open the excel file on one drive from regular desktop excel and refresh the query and overwrite the excising file ? Do queues get lost when saving to one drive ?
I consider better to save the excel files in Sharepoint libraries because we can take advantage of the control versión features of these. Another good practice is to apply table format to the data area in the excel sheet because Power query detect it like any table of a database.
OneDrive for Business and SharePoint Online are very similar now as they merged. That is the recommendation I make to folks if they want versioning. Although when they want versioning, that isn't usually what they are talking about. They want source control on the different aspects of Power BI (Model, report, M Scripts, etc...)
Nice movie. I hope the information to clear permission of the path using Menu 'File' - 'Options - Setting' - 'Data Source settings'. for set authentication.
Is this also possible with folders? I would like to have a folder with sales data excel sheets for each year seperate, so that I just have to update and refresh the current year. In Power Bi Desktop I imported a folder and connected all the sheets inside this folder, so that I just have one table in Power Bi. Can I do the same using One Drive?
Hi!, thanks for the information. Do you know if is possible to get excel data in power bi but with the URL?. i mean without using the share point?? Thanks
In our organisation the data comes from Autodesk BIM360 and want a solution to replace the file on Onedrive with fresh data [filename] stays the same BUT does the URL change every time a file is overwritten?
Hi Guys, Can you explain why this feature will not be enabled on PBIRS? Kind of sucks that I cannot share it within our company for free even though we have the Report Server included. Is there a way to accomplish the same effect on PBIRS?
Great Video! Is there a way to use One Drive to connect a folder with multiple excel files? I have done some research but haven’t found many info about this.
And if you need someone add data in that excel you could use forms too. In that way you could avoid some errors when the people add info, for example a date. (ddmm or mmdd) 07/06 06/07
Well it seems that if you don't have any other data required a "Gateway" it is all fine, but if you are mixing data sources, i.e. some from OneDrive and others from a SQL database, then it won't work cause it will insist on adding the OneDrive datasource to the "Gateway" which doesn't support OAuth2 authentication.
Hello Everyone, I have one question, can this setup allow us to go on PBI Service and refresh the dashboard from there to see the change. Kindly let me know if there any way where I can updated my excel online and it reflects on PBI Service Dashboard without publishing it again.
I am having problem when I publish the report you did ( a similar one) to the workspace, then it does not get updated anymore. Somebody has the same issue?
hello there..i got some problems here..when i try to connect/sign in to the organizational acc, this appear.. "Could not load type 'System.Runtime..............................'. hope to hear from you soon :)
Good that i googled before i connect to one drive and your solution comes first of all the list. This lesson will remain for ever. Thanks a lot!
THANK YOU! I've subscribed. This is the first time I've been able to work with real data and PowerBI and get it to work. Thanks so much!!
This video saved me 2 weeks of work from getting ruined + saved my summer job. Thanks
Just here to say that I love your explanations Patrick! It's 3:00 AM and I am studying for the PL-300 and your way of explaining made me more engaged. Keep up this channel guys!
Thank you! There are a million different links one could use, and you found the only one that works. Greatly appreciated.
You just saved my life. For all the people watching this tutorial now, if you can't find the link in your webbrowser, open the excel sheet in your app and then go to Information and copy path. :)
Still loving your videos. It is my go-to resource for Power BI learning.
Love how cool you are explaining all of these, Bro. Thank you and more power to you!
You saved me big time, mates !!!! I've wasted my whole day looking for solution like this. Thanks a million!!!
Thanks man, I had to make a good impression with a colleague at work and this is the short explanation I needed!
Been looking how to do this for a bit. Thanks very much for the great video!
I have been looking for this answer for weeks! Thanks so much!!!
Thank you so much! I have been on PowerBI for atleast 2 hours but only found answers in this video.
Thank you! This is what exactly I am looking for.
Great tutorial, thanks Guy in a Cube!
WOW SO SIMPLE SOLUTION. You just saved my life. TKSSSS!!!!!
Amazing ! A giant step ! thanks to make it so simple !
i really like the way this guy talks and conduct the video, makes me feel even more excited to do more reports lol
Thank you for this view .
Please view how can replace path of dataset from hard desk into one drive or Google drive after i finished my project.
Thank you! This is awesome!
We have been using Sharepoint List / Folder (combining multiple csv/excel files) for all our PBI Models and Dataflows and it's awesome!!!
Which conector are you using? Web or really sharepoint? Thanks :)
@@josesousa2004 Sharepoint
@@deepakagrawal465 And you don't have problems publishing to web and refreshing the dataset?
@@josesousa2004 We don't publish to web. There is no problem in refresh. In fact, the refresh is much more faster than on-prem gateway.
@@deepakagrawal465 Cool :)
Thank you, it really helps me! Saludos desde México!
Thank you so much for this!! I couldn’t figure this out for months 😓😓
That is cool !! And it’s actually solving one of my problems !
Thank you, worked for me
Excelent!!! This tip was helpful to me!!
Excelente!
Muito obrigado!
Amazing!
Thanks man!
Just what I needed.
Nicely done!
you are the best, only now i found the answer to my question.Thank you very very very very much.
Sir, I want to thank you from all my heart ❤️
U are an absolute hero - I’ve been trying to figure out where to cut off the pathway FOUREHVERRR
Hats off ! Awsome !!!
hugely helpful!
Hi patrick, have been following your videos for quite sometime now and i like your tricks and tips. in my organization we are trying to connect to Sap bw database from Power bi using sso and have been able to do from power bi desktop but when we try to add the database in gateway we get error and we have been following up with Microsoft from last 4 months on this issue but support guys are not that technical experts. so wanted to check if you have ever tried connecting Sap database to Power Bi and adding it in Gateway. if yes please show us how you did it. I bet this is the biggest requirement for most of the big organization.
Hey Patrick, you are my problem Solver....Love your videos
Been wondering how to do that. Thanks man
Glad it helped you Royce! 👊
Thank you very much.
Sweet, was looking for this..
Great to hear! Hopefully it got you going.
this has saved my life!
Thank you GuyanaCube
HI, Your video is Awesome. If I want to automate the refresh in web, How I can do that.
Patrick, awesome presentation and Very fun to watch. you're the man for the job. You should teach kids in the college :)
hmmmm Professor Patrick. 😎 Appreciate the kind words! 👊
This is helpful!!
Now I am ready to impress my boss! :)
Awesome!👊
That's simply awesome. Cheers, Patrick
@Patrick: You said so many times Power BI is smart but unfortunately is not quite easy :( I know every user, even me, had use Get Data>Excel.
Thanks for this video and sharing your knowledge!
You rock!
Thank you!!
I do it the following way: Exporting SAP-Tables as csv Files in Onedrive Folder at night and syncing with Sharepoint Website Folder. Running PowerBI Reports (Data Source Sharepoint Folder) in the PBI Service with a scheduled refresh every morning. Works perfectly!
Ralf Hanrieder awesome! How can you export sap tables automatically?
@@PedroFernandezRuiz Our customer, who does not use the SAP BW, wrote a small program to export the original data tables automatically. We use 35 SAP Data Tables over most SAP modules with a daily update and additional some Excels in our PowerBI-project. Another customer uses the BEx Analyzer of SAP to export the data. Hope this helps?
@@implementedgmbh Do you need a Gateway to have the scheduled refresh on Power BI Service?
@@goristhibaut5817 No, you do not need a gateway. OneDrive is syncing all data from the OneDrive Folder on the local machine to Sharepoint (Cloud). The Connection from PowerBI (Service) to the data is via "Sharepoint Folder", so you do not need a Gateway from PowerBI to a local machine. You only need a Sharepoint Connection to get a scheduled refresh in PowerBI Service. Ok?
@@implementedgmbh OK thanks for the information
Hi thanks for this video
I need to know that can put live pivot table from one drive link?
If it is can you please make a video on that it will be too helpful
Thank you very much. That was very helpful.
Thank you, it is very much appreciated! 👊
👊
Good explanation... and I smash LIKE... BUT... with google sheets, I don't have to click refresh. The realtime updates are crucial for coordinating complex events.
the video and delivery are awesome. but i have a question here, with the same purpose, is this the "official" method or a workaround? i mean you have to edit the url first in the notepad (?)
This video is awesome! Unfortunately, I cannot get a connection with my Gateway. Do you have an idea what it could be?
Hi i have the same issue
cool
...very helpful...
what if the connection is already using excel offline, is there a way to convert everything to excel online (onedrive)?
so I don't have to do a reset on the dashboard
Thanks for this!
I'm doing this with a report im working on. Every now and then I get an error in the refresh when i REPLACE the file, even with same filename, sheet name and columns (its a CSV actually, but same sheetname still). I get errors when I REPLACE, but not when I edit the file and add the new rows manually. Why/how is that? Thanks again!
I know its almost a year late but would it work the same if the excel file lives on a SharePoint and not on a OneDrive? Thanks for all the amazing videos!
Great.. For this,Is there a way to refresh the report with out manually or auto schedule refresh dataset. I meant like direct query
Yes, if you go to Power BI service and click "Get data" and choose onedrive, then create a report on that dataset. Directly in the PBI service, or if you want to do it in Power BI desktop using Power BI datasets source. The updates from the excel will be instantaneous.
You saved me again!
Hi Patrick, great job. I have been struggling long. i have a an app which uses 2010 access mdb. is there any method where i can access that in power bi for reports , since it says that its in previous version
Amazing! Greeting from Perú
Yoooo! Thanks for watching!! 👊
cool
thanks
Can I like this 20,000 times!? Thank you!
haha love it! wish you could. Thanks for watching! 👊
Hi Patrick, I am finally trying this method and I think it’s great. However I save my Power BI files on OneDrive and I’ve been getting errors, mostly about the excel file being corrupt. I’ve read somewhere that it could be caused by sync issues. Any ideas?
Is this a way to get around the PowerBI email restriction that prevents collaboration between people who aren't on the same email domain?
Thanks for sharing Patrick 👍
What about onedrive folder?
You can do this from a folder perspective to. Treat it as a SharePoint folder.
If you don’t have a data gateway, but have office 365 / one drive, can you save an excel file with power queries which update data then save that file to one drive ? How would you upload it so it’s a refreshable file ? Or can you open the excel file on one drive from regular desktop excel and refresh the query and overwrite the excising file ? Do queues get lost when saving to one drive ?
thanks!!!! nice!!!
TY for this)
You're welcome. Thanks for watching! 👊
I consider better to save the excel files in Sharepoint libraries because we can take advantage of the control versión features of these. Another good practice is to apply table format to the data area in the excel sheet because Power query detect it like any table of a database.
Version control is available in One drive for business..
OneDrive for Business and SharePoint Online are very similar now as they merged. That is the recommendation I make to folks if they want versioning. Although when they want versioning, that isn't usually what they are talking about. They want source control on the different aspects of Power BI (Model, report, M Scripts, etc...)
Hey Patrick
I have a file in one drive (peronal) and with my team member... I wanted to pull information by power query. Is it possible please explain
@guyinacube can we update the excel sheet data from Power BI?
Nice movie.
I hope the information to clear permission of the path using Menu 'File' - 'Options - Setting' - 'Data Source settings'.
for set authentication.
Ohh great. this is what i am looking for. Thanks
Hi Guys. What about when you already have a file setup, with all links etc. I don't really want to start all over again by doing these steps?
Hi patrick, works like a charm, but refresh does not refresh the reports, only the dataset. Solution?
Hi, great vide! :)
Like you said thumbs up
GREAT SIR
Thank you! 👊
You rock
Hey!!
Is a way to sync multiple excel sheets together with Power BI
Is this also possible with folders? I would like to have a folder with sales data excel sheets for each year seperate, so that I just have to update and refresh the current year. In Power Bi Desktop I imported a folder and connected all the sheets inside this folder, so that I just have one table in Power Bi. Can I do the same using One Drive?
@patrickdba, for some reason I cant get back to Access Web Content window. It always passes by it. Hence, I can't get it through. Help?
Hello! just my curiosity,... will it refresh once the data is published online?
Hi!, thanks for the information.
Do you know if is possible to get excel data in power bi but with the URL?. i mean without using the share point??
Thanks
I also wanna know that
In our organisation the data comes from Autodesk BIM360 and want a solution to replace the file on Onedrive with fresh data [filename] stays the same BUT does the URL change every time a file is overwritten?
Hi Guys, Can you explain why this feature will not be enabled on PBIRS? Kind of sucks that I cannot share it within our company for free even though we have the Report Server included. Is there a way to accomplish the same effect on PBIRS?
Any tip if my file is only xls and not xlsx ? is there any automatic way to convert it ? on free onedrive ?
How to Connect Most Recent File dynamically from azure data lake Folder in power bi ? please i need an answer ,thanks.
I can't find a way to make it working when I publish the dashboard. Even though I specified Org auth in the desktop I can't specify the same online
quick question, can i connect to more than one OneDrive file in the same Power BI?
Hi , how do we update the source if the file name is changing everyday as per the date but the structure and format of the file is exactly same ?
Great Video! Is there a way to use One Drive to connect a folder with multiple excel files? I have done some research but haven’t found many info about this.
same question
Hi Patrick, I am not getting the option to put in the organization details to sing in, how do you got that, please help
Brilliant!
YES!! 👊
I solved a task that has got me thinking for about a month in less than a hour today. You deserve some accolades. Thank you for a brilliant job
And if you need someone add data in that excel you could use forms too. In that way you could avoid some errors when the people add info, for example a date. (ddmm or mmdd) 07/06 06/07
Great! Awesome. thanks for sharing. Will it still require "Gateway" for PowerBI service?
Nope, the gateway is only needed for on premise sources. This is a huge advantage of using cloud sources with Power BI!
As long as data is in cloud, Gateways are not required
Well it seems that if you don't have any other data required a "Gateway" it is all fine, but if you are mixing data sources, i.e. some from OneDrive and others from a SQL database, then it won't work cause it will insist on adding the OneDrive datasource to the "Gateway" which doesn't support OAuth2 authentication.
Hello Everyone, I have one question, can this setup allow us to go on PBI Service and refresh the dashboard from there to see the change. Kindly let me know if there any way where I can updated my excel online and it reflects on PBI Service Dashboard without publishing it again.
I am having problem when I publish the report you did ( a similar one) to the workspace, then it does not get updated anymore. Somebody has the same issue?
how can you do this if the data you want to add is in a folder in onedrive (you want to use all the files in that folder)
Hey, thankyou for the solution but It's giving an error while refreshing the data when I'm opening the file in different laptop.
hello there..i got some problems here..when i try to connect/sign in to the organizational acc, this appear.. "Could not load type 'System.Runtime..............................'.
hope to hear from you soon :)
What if I want to get a bunch of files from OneDrive? How to get data from a folder on OneDrive?