Change excel data source in Power BI report

[NannaMi]

Hi,

Is it possible to change the excel data source by pointing at a new excel file, as I do not wish to overwrite the existing excel file, used as data source?

I have created a Power BI report with a local excel data source, called e.g. "March data".

Now I want to update the Power BI report with an updated excel file (containing same columns), but the file is now called "April data".

Is this possible? Since I do not want to overwrite the exisiting "March data" file with the data from the "April data" file.

[Greg_Deckler]

Yes, use the View | Advanced Editor and you should see a first two lines of something like:

let
    Source = Csv.Document(File.Contents("C:\temp\powerbi\dates.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),

Edit the path of your file and you should be in business.

[syldia]

Can I change the file type as well? Excel to online mySQL db table?

[arj777]

Hi,

I am also facing same problem. I built report in power bi using excel as data source, now want to replace it wqith sql server. Is there a method to do this?

[PraveenKumar]

I successfully changed data source from Excel to SQL table by following below steps

1. Create a new Power BI wokbook and connect to SQL database 

2. In the new work book, go to Home --> Edit Queries --> Advanced Editor. Copy the query. It might look some thing like this

let
Source = Sql.Databases("YOURSQLSERVERNAME"),
Now_Datamart = Source{[Name="YOURDATABASENAME"]}[Data],
dbo_Tag_data = Now_Datamart{[Schema="dbo",Item="YOURTABLENAME"]}[Data]
in
dbo_Tag_data

3. Perform #2 for Excel as well and your query might look like this

let
Source = Excel.Workbook(File.Contents("I:\Power\Shipcom\Ride Analyzer\Sample_Data.xlsx"), null, true),
Data1_Sheet = Source{[Item="Data1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Data1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"TripID", Int64.Type}, {"Date #", Int64.Type}, {"Trip Date", type date}, {"Day", type text}, {"Planned Duration (Hrs)", type number}, {"Actual Duration (Hrs)", type number}, {"Delay (Hrs)", type number}, {"Vehicle Type", type text}, {"Passengers", type text}, {"RAND13", Int64.Type}, {"Trip Legs", type text}, {"Booking Type", type text}, {"Route Name", type text}, {"Driver", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"TripID", "Trips"}})
in
#"Renamed Columns"

4. Replace excel connection with SQL connection

let
Source = Sql.Databases("YOURSQLSERVERNAME"),
Now_Datamart = Source{[Name="YOURDATABASENAME"]}[Data],
dbo_Tag_data = Now_Datamart{[Schema="dbo",Item="YOURTABLENAME"]}[Data]
in
dbo_Tag_data

5. Close & Apply

Power BI will take some time to read all the data from table (table should have the same format including data types and data that excel had before making changes) and exact reports would be generated. If there are any changes you did on excel data, you might want to repeat same.

Let me know if it works !!!