Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi all,
What's the best practice for file location and source data location I'm new to the orgnasation and novice to PBI so hopefully can get some answers from you.
I have few excel files to share around which has data connected to few local folders (not much data and can easily move around)
The organisation has pretty much everything saved on Sharepoint, though the local shared drive is still availble but unless that's the only option they do not like that idea.
So far I have tried to upload source data to sharepoint and reset the path, it seems taking very very long time to refreah; in the past I would defenitely set everything in share drvie where people can access
Question:
What's the best practice for file location and source data location in this senario?
Thanks in advance
Wendy
Solved! Go to Solution.
Hello @WLou
we have a similar situation in our company. You have to differenciate if the person using the report need only to watch it or if they need also to update it.
If they only need to watch it, it doesn't matter were you stored the file, as long as the people that have to refresh the data have access. If you everyone also needs to refresh it, choose the infrastructure available in your company (shared folder, sharepoint). This is more a decision of your admin because of authorizations issues for every platform.
Hope this helps
Jimmy
Not sure why .Files would work and .Contents would not. Are you using SharePoint online or is it on prem? Your URL seems to indicate online, but you only have one namespace after the .com. I have to have two - /sites/SiteName
You cannot browse at the sites level.
Other than that you might need to contact your IT department about permissions. I know they can disable SharePoint browsing in Power Query, but was unaware they could let SharePoint.Files work and block SharePoint.Contents.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHello @WLou
have you been able to solve the problem with the replies given?
If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
All the best
Jimmy
Depends on the data, but SharePoint is often fine. The problem is when you connect to a SharePoint folder, Power Query generates a statement like =SharePoint.Files("https://blahblah")
That will scan the entire sharepoint site.
If the relevant files per report/query are in one folder, change SharePoint.Files to SharePoint.Contents and leave the rest of the function the same. It will then let you quickly navigate to the folder where the data is and get a file or combine multiple files. But it only works if all files are in the same folder. If not, then you have to stick with SharePoint.Files and filter down the list some other way, and that takes a long time.
You could use something like Azure DataLake, but that really wants data on a per-folder too.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank you all for your contribtes, it defenitely clearify things
@edhans one more question, https://xxxxxx.sharepoint.com/xxxxx/ is the link I use to connect, and if I add another level of folder after site it errors
and even I succeed to add another folder which narrows down the search, it still contains 50 odd subfolders after that
If I ask IT to set up a folder at higher leverl woudl it help?
Regards,
Wendy
@WLou you don't add folders in the main URL.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSorry to be a pain, just changed Files to Contents in query editor and its giving me "Expression.Error: Access to the resource is forbidden." does it means there a certain file that has been locked away?
FROM
let
Source = SharePoint.Files("https://xxxxxx.sharepoint.com/xxx/", [ApiVersion = 15])
in
Source
TO
let
Source = SharePoint.Contents("https://xxxxx.sharepoint.com/xxx/", [ApiVersion = 15])
in
Source
Not sure why .Files would work and .Contents would not. Are you using SharePoint online or is it on prem? Your URL seems to indicate online, but you only have one namespace after the .com. I have to have two - /sites/SiteName
You cannot browse at the sites level.
Other than that you might need to contact your IT department about permissions. I know they can disable SharePoint browsing in Power Query, but was unaware they could let SharePoint.Files work and block SharePoint.Contents.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThese days I would say Azure File Storage but this is a highly subjective question. I do not think that there is a one size fits all best practice for this.
Thank you Greg
Not sure if its worth getting for only few files to be shared?
The reason of having excel files is the drill down function of the pivot table, where PBI can only down to certain level
I'm planning to have majority of the reports done via PBI and distribute to the wider audiance so I guess no need to worry where to store the data?
Regards,
Wendy
Hello @WLou
we have a similar situation in our company. You have to differenciate if the person using the report need only to watch it or if they need also to update it.
If they only need to watch it, it doesn't matter were you stored the file, as long as the people that have to refresh the data have access. If you everyone also needs to refresh it, choose the infrastructure available in your company (shared folder, sharepoint). This is more a decision of your admin because of authorizations issues for every platform.
Hope this helps
Jimmy