Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NannaMi
New Member

Change excel data source in Power BI report

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.

4 REPLIES 4
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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?

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 !!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.