Power BI report how to swap dataset used by report from local csv to Sharepoint csv

[curtisp]

I created a report using Power BI Desktop with dataset from a local csv file. I have published that report to Power BI Service (Free version). It works fine.

But now I want to source the report's dataset from a csv file in my Office 365 E3 Sharepoint site.  I have successfully created a new dataset in Power BI Service from the Sharepoint csv.

Now I want to swap my published report's dataset for this new Sharepoint source dataset.

Can I swap them in Power BI Service, or do I have redo the report recreating the dataset source using the Sharepoint csv file, and then republish the updated report to Power BI Service?

Is there some programmatic way of doing this? Eg with Power BI API can I change report datasource? 

[ankitpatira · Accepted solution]

@curtisp First time you did report with local csv file and published, you would have pbix (power bi desktop) file saved locally. Using that you can change data source to SharePoint and re-publish that report. You can't change data source in power bi service.

[reportsgroup]

Hi @ankitpatira @curtisp 

could u help with similar item here.

so i got multiple reports, using same dataset. filter just by different countries. the dataset is queried from ODBC amazon redshift

report1_a
report2_b..

and we are doing scheduled refresh for all of them. with the heavy load, issues pop up.

and realized there is something called powerbi datasets, which can takes dataset of a specified powerbi to make use of. ideally it would be we only refresh one dataset and the other will get reflected to the changes or addition on it.

tested on an empty powerbi, powerbi dataset from report1_a. got the whole data. and very much fast also. but not particularly sure on how to swap it to keep visual in report2_b and others.

report1_a is the main one, the dataset.

so i would add in the powerbi dataset from report1_a into report2_b.

so how can i change the visuals in report2_b to use the new dataset ya? step by step would be great, summary of it also works.

tldr: how can i convert data set and keep the visual, swap odbc amazon redshift to powerbi datasets

[curtisp]

Great thanks, I did update the report datasets in Desktop to use the Sharepoint files.  Then after I uploaded the report to Power BI Service, I was able to schedule the refresh from Sharepoint.

The dataset Power Query needed to be changed from using the local text file:

let
Source = Csv.Document(File.Contents("C:\path\to\my\file\datafile.csv"),[Delimiter=",", Encoding=1252]),
#"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV")
in
#"Promoted Headers"

To this using the Sharepoint file:

let
Source = SharePoint.Files("https://mydomain.sharepoint.com", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "datafile.csv")),
#"Combined Binaries" = Binary.Combine(#"Filtered Rows"[Content]),
#"Imported CSV" = Csv.Document(#"Combined Binaries",[Delimiter=",", Columns=11, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV")
in
#"Promoted Headers"

(Note I filtered to get the file I wanted in this dataset because my Sharepoint folder has many files)