Need help for getting data from Sharepoint

[Anonymous]

when I want to make reports on Desktop,  PBI requires the root URL of it.

The question is that there are lots of data files in the root URL and I can't select the specific folder or data file from it.

What can I do to solve it???

I just want to get data from the specific folder or file in sharepoint.

[Mariusz]

Hi @Anonymous 

You can filter the file path column ( I think its called folder path if not there is a column with a path   ) to contain the name of your folder, this will limit the rows to files contained within this folder.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Hi @Mariusz 

Thanks for help.

The point is that power bi requires me to paste the root URL(Sharepoint), it can't recognize the specific path URL.

When I want to get data from specific path URL. it will encounter an error like this:

[mahoneypat]

You need to use the site (or subsite) URL only in the SHarepoint Folder connector.  From there you can filter down using the FolderPath column or Filter on the filename (or use a text filter like Filename contains, etc.).  The other option for a single Excel file is to use the Web connector to with its full url (minus "?web=1").

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Hi there@mahoneypat 

Thanks for your suggestion.

I try to paste the folderpath URL in the Sharepoint Folder connector, but It still can't recognize the specific URL (like https://company.sharepoint.com/sites/7165/Market%20Intelligence/),it requires the root URL.

And if I paste the root URL( like https://company.sharepoint.com/sites/7165/), it will load all the files in the folderpath, and I can't select the specific folder .

[v-shex-msft]

HI @Anonymous,

Yes, current power bi seems not able to authorization the subfolder to get the file lists. For your requirement, I'd like to suggest you export the file name list to a local file.
Then you can get data from this file to power bi and add a custom column to invoke them with 'web' connector to concatenate 'subfolder URL' and 'filename'.

Regards,

Xiaoxin Sheng

[mahoneypat · Accepted solution]

Now I think I get your issue.  You can't filter on Folder Path in that dialog box to let you combine files in a specific folder.  If you click on Transform Data instead of Combine, you can then filter on Folder Path.  Once you get to the folder you want, you can then hit the Combine button on the right of the first column with all the Binary hyperlinks.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Hi @mahoneypat 

Thnks very much for your solution. It works but I encounter another two problems:

1.The query can only load up to 1000 files and my root url contains too many files and there are still many files I can't get it;

2.If I can set the sharepoint folder as database to make reports in power bi? 

[mahoneypat]

1. Couple Options - You can use Text Filters in the Filename and/or Folder Path columns to find your content.  Also, you can try changing SharePoint.Files to SharePoint.Contents in the Source step of your query.  Depending on where your content is stored, that may be a simpler view of things.

2. Not sure what you mean, but you can analyze the file metadata of all the files by simply not combining the files.  The table of filenames, folders, modified dates, etc. is just another table you can analyze.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat