Excel data source name change

[Matthias93]

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

[Seth_C_Bauer · Accepted solution]

@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. 

[tringuyenminh92 · Accepted solution]

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.

[Seth_C_Bauer · Accepted solution]

@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. 

[TimKroemer]

@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

[Seth_C_Bauer]

@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.

[thumah2013]

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

[tringuyenminh92]

Hi @thumah2013,

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

[TimKroemer]

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

Gratefully,

Tim