Change a Power BI CSV or Excel Datasource from File Server to SharePoint Online

 

I've recently had some Power BI reports that reference Excel and CSV files stored on a file server as data sources. To get around using an on-prem data gateway, I changed the source of the file to be in a SharePoint Online document library. Here is how I did that:

1) Open the report and open Power Query Editor by clicking the Transform Data button.

2) Right click on the query you want to change, and select Advanced Editor

3) Copy the entire query to a notepad file for later (you don't need to save it)

4) Replace the entire query with this (replacing the URL of the site with your site), and click Done

let

    Source = SharePoint.Contents("https://contoso.sharepoint.com/sites/ReportSite/", [ApiVersion = 15])

in

    Source

5) Navigate to the file that you want (make sure that you've copied the original source file from the file server to a SharePoint document library first), by clicking in the Content column. Click Table to navigate folders, and click Binary when you get to the file you want to pull in.

6) Right click the query again, and select Advanced Editor

7) It should now have added four or more rows to the query. Here is an example:

let

    Source = SharePoint.Contents("https://contoso.sharepoint.com/sites/ReportSite/", [ApiVersion = 15]),

    DashboardFiles = Source{[Name="DashboardFiles"]}[Content],

    #"data csv" = DashboardFiles{[Name="data.csv"]}[Content],

    #"Imported CSV" = Csv.Document(#"data csv",[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),

    #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Device Name", type text}, {"Duration (In Seconds)", Int64.Type}, {"StartTime", type datetime}, {"EndTime", type datetime}, {"PeopleCount", type text}, {"Device Id", type text}})

in

    #"Changed Type"

Your query will look slightly different than this based on your names and whether you are using a CSV or XLSX spreadsheet.

8) add a comma at the end of the "#"Changed Type" = " line.

9) Go to your notepad and find the first instance of Changed Type in your query. Select and copy everything below it.

10) In the advanced query editor, paste it below the last entry in the query, replacing the "in #"Changed Type""

11) Click Done and you have successfully changed the data source to a spreadsheet sitting in SharePoint Online.

Share on Google Plus

About Tom DeMeulenaere

Highly accomplished information technology professional with extensive knowledge in System Center Configuration Manager, Windows Server, SharePoint, and Office 365.