Power Query Function / Get Data from Sharepoint Folder

Used for anything related to the new Power BI Desktop and Service tools
a68tbird
Posts: 32
Joined: Sat Mar 19, 2016 4:58 am
Location: Toronto, ON

Power Query Function / Get Data from Sharepoint Folder

Postby a68tbird » Tue Oct 18, 2016 3:27 am

Hi Matt,
You helped me previously with an excellent solution for combining CSV files (viewtopic.php?f=5&t=499). I'm now hoping I can adapt this same methodology to use in a Power BI solution.

The Excel report that I have is great, however after I publish it to our company's OneDrive, it loses the slicer functionalities (apparently not supported in Excel Browser). Publishing to Power BI means I can share the fully functional report with everyone in our company.

I tried the following (note this is just an abbreviation, only showing the first few rows of the query that are related to the path and file names):

Code: Select all

(myPath,myFile) =>

let
    Source = SharePoint.Files("myPath", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".CSV")),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Text.Contains([Name], "OFFICIAL")),
    #"myFile&_&myPath&Documents/Soundscan/WeeklyReport/" = #"Filtered Rows1"{[Name="myFile",#"Folder Path"="myPath&Documents/Soundscan/WeeklyReport/"]}[Content],
    #"Imported CSV" = Csv.Document(#"myFile&_&myPath&Documents/Soundscan/WeeklyReport/",[Delimiter=",", Columns=14, Encoding=1252, QuoteStyle=QuoteStyle.None]),


I tried invoking the function using the following as my parameters:
myPath = https://ourcompany365-my.sharepoint.com/personal/user_name_ourcompany_com/
myFile = 20150311065701-OFFICIAL.csv

I receive the following error:
An error occurred in the ‘’ query. DataSource.Error: SharePoint: Request failed: The remote name could not be resolved: 'mypath'
Details:
DataSourceKind=SharePoint
DataSourcePath=http://mypath/


Thoughts?
Thanks very much.
Travis

a68tbird
Posts: 32
Joined: Sat Mar 19, 2016 4:58 am
Location: Toronto, ON

Re: Power Query Function / Get Data from Sharepoint Folder

Postby a68tbird » Wed Oct 19, 2016 5:04 am

Hi -
Just wanted to share that I managed to figure this out. Instead of using Get Data > Sharepoint Folder, I instead used Get Data > Web and then pointed to the Sharepoint URL where a file lives. It's important to make sure that the URL is free of special characters, or query expressions. I found useful information here: https://medium.com/@Konstantinos_Ioannou/onedrive-powerbi-desktop-use-valid-paths-to-import-data-stored-in-onedrive-for-business-60089848e594#.hk156c8h2.

I then applied the same steps that Matt outlined in this article: http://exceleratorbi.com.au/combine-excel-workbooks-power-query-method-1/.

I can now save the weekly files to the Sharepoint folder, refresh my published PowerBI report to display up-to-date results for all members of our company to view.

Travis


Return to “Power BI Desktop/Service”

Who is online

Users browsing this forum: No registered users and 4 guests