Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hugoventurini
Advocate II
Advocate II

Connect Power BI Desktop to file folder on OneDrive for Business

Hello;

I need to connect the Power Bi desktop to a file folder in OneDrive for Business, similarly to a file folder located in the c directory of any computer, and then write the data extraction routine using the " Excel.Workbook ", for example. Has anyone had any experience like this? It's possible?

2 ACCEPTED SOLUTIONS

@hugoventurini,

There is an idea about this issue, please vote it up.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/19061959-enable-connectivity-to-a...

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

v-yuezhe-msft
Employee
Employee

@hugoventurini,

In the latest version of Power BI Desktop(December update), please enter the following URL in SharePoint Folder connector, this way, you can connect to OneDrive for Business folder in Power BI Desktop.

https://mydomain-my.sharepoint.com/personal/user_mydomain_com

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

17 REPLIES 17
yossifisch
Advocate I
Advocate I

Check out my updated response here: https://community.powerbi.com/t5/Integrations-with-Files-and/Need-to-load-all-Excel-files-in-a-given...

This is a much faster and intuitive solution.

yossifisch
Advocate I
Advocate I
v-yuezhe-msft
Employee
Employee

@hugoventurini,

In the latest version of Power BI Desktop(December update), please enter the following URL in SharePoint Folder connector, this way, you can connect to OneDrive for Business folder in Power BI Desktop.

https://mydomain-my.sharepoint.com/personal/user_mydomain_com

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you Lydia, but I have a problem with my user name. It seams that because my user name is firstname.lastname@domain.cl it does not work. We tried with other colleages who had no peroid in the user and it work...Any solution to that? Thanks again,

 

María 

Replace the period with an underscore, firstname_lastname_domain_cl

See this page for details: https://docs.microsoft.com/en-us/onedrive/list-onedrive-urls

The solution proposed by @v-yuezhe-msft works, however with a serious caveat: you can only connect to the root folder of OneDrive, not to subfolders, which would be the correct and ideal. To restrict the query it is necessary to open the Query Editor and filter through the column that shows the path to the subfolder, which is what works, however, is inefficient. The ideal solution would be to allow connection to one or more subfolders within the OneDrive root folder, depending on the context of the project you are working on. Even so I am very happy, because it represents a breakthrough. I'll point it out as solved, but making clear here the need for an enhancement that allows access to subfolders. I will also test the solution proposed by @yossifisch, whom I also thank for the help.

You're all amazing, thank you!

I think my solution is basically what you just outlined, connecting to SharePoint and expanding restrincting the folder using the Query Editor.

Anyway, I left a suggestion to improve this feature so that it allows us to connect directly to the subfolders. If you deem it interesting, like me, I ask you the kindness to vote: 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/32923258-allow-direct-access-to-t...

Thank you!

Anonymous
Not applicable

@hugoventurini Thanks very much for proposing this feature, i have voted it up Hugo. I am attempting to do to the same thing, which is to iterate through a OneDrive for Business FOLDER (and not just a single file). Is there any new/updated approach to do this now? As you mentioned, it is more difficult to edit and filter to specific files from the root , but rather there should be a way to connect directly to a folder. 

@Anonymous As explained here, just switch SharePoint.Files to SharePoint.Contents and you'll be able to navigate to your specific folder without filtering. This solution is simple, fast and effective, it just requires you to make that minor change in the formula bar as this is not an available option in Get Data.

Wow @v-yuezhe-msft; thanks Lydia!

This is very important to small business solutions developers. I'll try this solution now in this morning, and return to confirm.

 

 

v-yuezhe-msft
Employee
Employee

@hugoventurini,

There is no built-in connector for OneDrive for business folder in Power BI, it is not possible to directly connect to the source.

To work around the issue in your scenario, you can sync the OneDrive for Business files to local folder of your computer, then use Folder connector in Power BI Desktop to get data from the local folder.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks very much for your attention. I already use this solution described, but it is not ideal because it does not allow another user to update and republish the report. Is there any prospect of having this connection in the future?

@hugoventurini,

There is an idea about this issue, please vote it up.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/19061959-enable-connectivity-to-a...

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

The link to the solution no longer work, is there another work around for the solution?

That was a link to an idea, not sure what happened with that idea but Hugo's idea is still up and accepting votes here.

 

I find SharePoint.Contents to be an efficient solution.

  • Start a blank query (Get data > Blank query)
  • Copy and paste this code in the formula bar:

 

= SharePoint.Contents("https://domaincom-my.sharepoint.com/personal/email_domain_com/", [ApiVersion = 15]){[Name="Documents"]}[Content]

 

  • Replace [domaincom] with your SharePoint domain, and [email_domain_com] with your login email. For exmaple, if your domain is mycompany.org and your email joe at mycompany.org,  you'd use:

 

= SharePoint.Contents("https://mycompanyorg-my.sharepoint.com/personal/joe_mycompany_org/", [ApiVersion = 15]){[Name="Documents"]}[Content]

 

  •  This should bring up the credentials window and, once logged in, should persent a table of all the files and folders in your OneDrive root folder
  • You can navigate to the desired folder by clicking on the 'Table' link in the 'Content' column next to the desired folder name

This does not work for a Folder. You can only connect to root. Any options?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors