Forum Discussion
Issue to get Data from "SharePoint Folder"
Hello dear community
I'm new here and started with PowerBi Desktop free version (some months ago) to prepare all my dashboards before moving to the"Pro" licence and share them with colleagues from my company.
I have to use excel files from my company SharePoint. I have a to agregate several files from direct directories to create what I need.
During my rampup phase I followed tthe following steps to create my dashboards
- I started with local files, everything was fine.
- I then used "sync" directories from the SharePoint to my PC, everything was fine (path: c:\users\myaccount\MyCompany\SharePoint-name\MyDirectory) - files are sync via OneDrive. Everything works, the Data refresh is quick.
I have the dasboards I wanted, but as I want to share them and refresh them automatically - I can't still use "Folter" as source but I have to change its type from "Folder" to "SharePoint Folder" (use of the SharePoint URL)
And this is where I face problem.
Even I'm trying to reach the same file locations using "SharePoint Folder", every action is extremely slow when I create new actions in a Query. I'm not sure I can replicate all actions I did in my previous dashboard into a new project using "SharePoint Folder".
I can add my SharePoint, authenticate properly, and see all files, but when I want to filter a specific directory (SharePoint-name\MyDirectory), then it starts to really slow down, and every action I want to add after this step is very slow.
I tried to solve my problem with CoPilot, but no success until now. I also tried to google solutions on the web, but without success until now.
I don't know if it's because my Company SharePoint contains a very large number of file, or for any reason it would be link to the fact that I'm using the free version for the moment... but I can't succeed using "SharePoint Folder" for the moment
I hope someone can help on this
thx in advance
Dan
Hello all
the solution works fine, it's as quick as using local files/paths
Wust the way the select directories which is a little different.
As all my files are store into the same SharePoint, I created 1 Query to select the SharePoint and went down up to the same common directory where all my other sub directories are stored.
Then need by need I add a new Query to continue going down hierarchy up to the expected file/files.
To go down hierarchy, you need to use the method I gave earlier - the result of the "source" query will be the list of files & directories (and/or libraries) viewable at the root of your SharePoint/
- You'll find the "content", "Name","Extension"....
- From "Name" column, > "Filter" to select the directory you need to go down hierarchy.
To continue ,
- Then expand "Content" column, where you just need to select "Content" and "Name" again > that will show you the lower level files & sub-directories in the hierarchy
- Repeat this up to the expected location
You can then select multiple files or a unique one to import Data
At the last selction (File level), if you need filter files by their "Date modified" or "Created", select those properties when expanding "Content" columns (if you do not select them, default Dates you will see are Sharepoint/Directories creation Dates, not the files real properties....)
The solution is working fine
👍
6 Replies
Hi again
I spent time looking for information on "SharePoint" connector... and it seems to be a known situation (to avoid saying an issue)..
I think I found a solution (another IA helped me to be honest 🙂 )
When using "SharePoint Folder", The SharePoint connector queries the Microsoft SharePoint API, and gets all files from all libraries of the sharepoint, and downloads the MetaData of each file.. which explains why it takes so long to get a result...
A solution is to modify the type od source query from:
= SharePoint.Files("https://Mysite.sharepoint.com/sites/SiteName", [ApiVersion = 15])
to :
= SharePoint.Contents("https://Mysite.sharepoint.com/sites/SiteName", [Implementation="2.0"])
with, the Connector navigates by directory instead of files
Then, we need to open/navigate into "Content" and deeg up to the expected directory
I did a try with one of my usecases and that works fine as expected.
I'm going to tests other of my usecases next week and will confirm if this works in all my cases
RegardsHi Dan_FR29,
Thanks for sharing your findings and detailed explanation. Good to hear that it is working fine in your test case
Please do test with your other scenarios as well, especially if you are combining multiple files from different directories.Once you confirm, it will be helpful if you can share your final approach here, so it can help others facing similar issue.
Regards,
Community Support team.Hello v-hjannapu
yes, that's my intention - I expect spending some time tomorrow or Wednesday to test that, and of course I 'll share my findings, if it can save time for others...
Regards
