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
Matthias93
Helper III
Helper III

Excel data source name change

Hi,

 

This may be a very basic question, but I was wondering how I can adapt my query to a name change in my excel data source. When I go to the advanced query editor, I currently have this source:

 

Source = Excel.Workbook(File.Contents("\\k2-a.be.example.com\DavWWWRoot\sites\DC\Project\DEV-RPM\Dashboard excels\Retention.xlsx"), null, true),

 

This excel is stored in a SharePoint document library. If I were to change the name of this excel on SharePoint, could I simply replace 'Retention.xlsx' to 'Retention2017,xlsx' for example without losing relationships and visuals connected to this excel?

 

Thanks in advance for helping

 

Kind regards,

Matt

2 ACCEPTED SOLUTIONS

@Matthias93 As long as it's just the name of the Excel file, then you shouldn't have any issues making the change as you describe.

If you are really converned about the PBIX, you can just "Save As" a copy of it and "test" the connection change with both files in the data source location. 


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

tringuyenminh92
Memorable Member
Memorable Member

Hi @Matthias93,

 

Yes, you can do that. but just ensure 2 excel files have same structure, header names. This is common way that used to change data source setting. 

https://community.powerbi.com/t5/Desktop/Best-Way-of-Moving-pbix-Calculations-Mesures-from-Running-o...

For safe, you could copy/clone your pbix file and try with copied file.Smiley Very Happy

View solution in original post

7 REPLIES 7
tringuyenminh92
Memorable Member
Memorable Member

Hi @Matthias93,

 

Yes, you can do that. but just ensure 2 excel files have same structure, header names. This is common way that used to change data source setting. 

https://community.powerbi.com/t5/Desktop/Best-Way-of-Moving-pbix-Calculations-Mesures-from-Running-o...

For safe, you could copy/clone your pbix file and try with copied file.Smiley Very Happy

@Matthias93 As long as it's just the name of the Excel file, then you shouldn't have any issues making the change as you describe.

If you are really converned about the PBIX, you can just "Save As" a copy of it and "test" the connection change with both files in the data source location. 


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

@Seth_C_Bauer Thank you for your reply Eno - can you also please help, what to do if the name of the source sheet has changed? I have a standard report but the name of that report has been changed - resulting in a different sheet name in the Excel file now containing the data - I can point PowerBI to the new file - but how do I tell it the new Excel sheet name? 

 

Thank you in advance for your time and help!

 

Gratefully,

 

Tim

@TimKroemer As long as the data structure hasn't changed you shouldn't have any issue editing the query.

Edit Queries -> Edit Queries -> Advanced Editor

Replace the file name under Source

And the tabs should show up something like "ztest_Sheet = Source{[Item="test",Kind="Sheet"]}[Data],"

You can see that originally I pulled in a tab called "ztest", then I changed the actual source file and changed the tab name to just "test" which I replace in that connection string. Works fine.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Hi @Seth_C_Bauer,

 

I realize this thread is several years old.  Hoping I might be able to ask a follow up question to this.  Your advice helped me to link my PBI report to newly names tabs (same format in the tabs).

 

My original add in for some tabs brought in blank columns of data, which appeared with names such as Column41.  I used the Applied Steps in the Query Editor to eliminate these from view.  However, the new tab that I have the report linked doesn't have these columns, so it isn't successfully pulling in the data.

 

Is there a way in the Advanced Editor to rid the report from even looking for this Column41?  I don't see an attachment option, so I am adding the Advanced Editor Code for one of the tabs:

 

let
    Source = Excel.Workbook(File.Contents("H:\Temporary\Tenneco_AM_NO_SCG_V5 (Set 1 Scenarios) 2.0 (Copy).xlsx"), null, true),
    #"Production Data_Sheet" = Source{[Item="OO_Productions",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Production Data_Sheet"),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ScenarioName", type text}, {"ScenarioID", Int64.Type}, {"SiteName", type text}, {"ProductName", type text}, {"PeriodName", type text}, {"ProductionQuantity", type number}, {"NetProductionQuantity", type number}, {"ByproductProductionQuantity", Int64.Type}, {"TotalProductionQuantity", type number}, {"ProductionWeight", type number}, {"ProductionCubic", type number}, {"ProductionCost", Int64.Type}, {"CO2", Int64.Type}, {"CO2Cost", Int64.Type}, {"TotalCost", Int64.Type}, {"PeriodNo", Int64.Type}, {"BOMCost", type any}, {"PolicyCost", type any}, {"ProcessCost", type any}, {"StepNumber", Int64.Type}, {"ID", Int64.Type}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type any}, {"Column34", type any}, {"Column35", type any}, {"Column36", type any}, {"Column37", type any}, {"Column38", type any}, {"Column39", type any}, {"Column40", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column22", "Column39", "Column38", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column40"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each true)
in
    #"Filtered Rows"

 

Sorry for the length, and appreciate any advice you can give!

 

BR,

-Alex

Hi @thumah2013,

 

Hope Handling Added & Missing columns in source topic of Chris Web - Expert in M Language could help you.

@Seth_C_Bauer Thank you very much for your time and help!

 

Gratefully,

 

Tim

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.