Connecting to sharepoint data source

[po]

Hi,

Have a working report which uses sharepoint as a source but to get this to work I had to connect to the all documents on the site and filter them and select file etc.

resulting power query below.

let
Source = SharePoint.Files("https://<company_name>.sharepoint.com/sites/BI/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".csv")),
#"workspaces csv_https://menziesdistribution sharepoint com/sites/BI/Shared Documents/Power BI/Workspace Inventory/" = #"Filtered Rows"{[Name="workspaces.csv",#"Folder Path"="https://menziesdistribution.sharepoint.com/sites/BI/Shared Documents/Power BI/Workspace Inventory/"]}[Content],
#"Imported CSV" = Csv.Document(#"workspaces csv_https://menziesdistribution sharepoint com/sites/BI/Shared Documents/Power BI/Workspace Inventory/",[Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Id", type text}, {"Name", type text}, {"Type", type text}, {"State", type text}, {"IsReadOnly", type logical}, {"IsOrphaned", type logical}, {"CapacityId", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Name", "WorkspaceName"}})
in
#"Renamed Columns"

Just wondering if easier better way of doing this e.g. tried source below but doesn't work.

Is there a quciker way to do this in tersm of code.  I'm just thinking whilst not that many documents on site if this grows speed will slow down a little.

https://<company_name>.sharepoint.com/sites/BI/Shared Documents/Power BI/Workspace Inventory/workspaces.csv

Thanks

[v-yingjl · Accepted solution]

Hi @po ,

If you want to get one specific file in SharePoint folder, I would suggest you to use Web connector instead of SharePoint connector to save some time.

URL will be like:

https://xxxx.sharepoint.com/sites/xxxx/Shared Documents/<file name>

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[po]

Hi,

Thanks for suggestion.

This looks to work but I had to go to teams and right clcik the link suchh that it accepts the url with %20 otherwise it was syaing invlaid URL.

https://<company>.sharepoint.com/sites/BI/Shared%20Documents/Power%20BI/Workspace%20Inventory/worksp...

Also,  chose organization and auth2 on the service.

Only thing is though if my password changes then will need to update the credentials.

Not sure what shoudl put for this - any thoughts and recommendations for this part?

[amitchandak]

@po ,

check if this can help

https://community.powerbi.com/t5/Desktop/Import-Data-from-an-Excel-sheet-in-SharePoint/td-p/47223
https://www.youtube.com/watch?v=3GIz50pftZ0
https://www.youtube.com/watch?v=gJF2e_43FRY
https://whitepages.unlimitedviz.com/2018/07/excel-files-power-bi-desktop-sharepoint/
https://docs.microsoft.com/en-us/power-bi/connect-data/service-excel-workbook-files
https://powerbi.tips/2016/09/loading-excel-files-from-sharepoint/