Changing data source to source with slightly different content

[Anonymous]

I would like to change my data source:

- FROM: An Excel file that I used to drop the data into, which then had a bunch of lookup columns appended to it in the same tab

- TO: An Excel file that contains just the underlying data, no additional lookup columns.

The raw data contains the exact same columns in the exact same order, just that the 'To' version doesn't have the additional lookup columns.

When I change the data source, I receive the following error "Data (the name of my data source). The column '{lookup column name that is no longer present in the To: file'] of the table wasn't found". Note that I removed these columns from my original data load.

I think I resolve this in advanced editor but cannot seem to figure it out. Here's the full text from the Advanced Editor. 

Very grateful for any assistance.

let
Source = Excel.Workbook(File.Contents("C:\Users\djaggard\Desktop\1.xlsx"), null, true),
Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Data_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Opportunity ID", type text}, {"Opportunity Name", type text}, {"Forecast Category", type text}, {"Service Offering", type text}, {"Account Name", type text}, {"Region", type text}, {"MRCE Currency", type text}, {"MRCE", type number}, {"MRCE (converted) Currency", type text}, {"MRCE (converted)", type number}, {"Weighted MRCE Currency", type text}, {"Weighted MRCE", type number}, {"Weighted MRCE (converted) Currency", type text}, {"Weighted MRCE (converted)", type number}, {"NRC Currency", type text}, {"NRC", type number}, {"NRC (converted) Currency", type text}, {"NRC (converted)", type number}, {"MRC Currency", type text}, {"MRC", type number}, {"MRC (converted) Currency", type text}, {"MRC (converted)", type number}, {"Primary Partner", type any}, {"Type", type text}, {"Lead Source", type text}, {"Created By", type text}, {"Opportunity Owner", type text}, {"Owner Role", type text}, {"Opportunity Owner Email", type text}, {"Close Date", type date}, {"Term (Months)", Int64.Type}, {"Contract Commencement Date", type date}, {"Contract End Date", type date}, {"G2000", Int64.Type}, {"Stage", type text}, {"Account Owner", type text}, {"Account Type", type text}, {"Industry", type text}, {"Parent Account ID", type text}, {"Stage Duration", Int64.Type}, {"Probability (%)", Int64.Type}, {"Age", Int64.Type}, {"Created Date", type date}, {"Fiscal Year", Int64.Type}, {"Fiscal Period", type text}, {"Pipe", type text}, {"Committed", Int64.Type}, {"Forecast", type text}, {"Sales Motion", type text}, {"Bill to?", type text}, {"Ship to?", type text}, {"Partner Name", type text}, {"Push Count", Int64.Type}, {"Last Stage Change Date", type date}, {"Type Consol", type any}, {"Stage_1", type text}, {"Region High", type any}, {"Fcst Exec", type text}, {"ACV", type number}, {"FY&Q", type text}, {"Close Month", Int64.Type}, {"G2000 Flag", type text}, {"Open Pipe?", type text}, {"EMEA Region", type any}, {"Region Consol", type any}, {"Week", type text}, {"Created Date Rule", type text}, {"Stage Change Date Rule", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Type Consol", "Region High", "Created Date Rule", "Stage Change Date Rule", "Week", "Region Consol", "EMEA Region", "Open Pipe?", "G2000 Flag", "Close Month", "FY&Q", "ACV", "Fcst Exec", "Stage_1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"MRCE (converted)", "MRCE USD"}, {"NRC (converted)", "NRC USD"}, {"MRC (converted)", "MRC USD"}})
in
#"Renamed Columns"

[AlB]

Hi @Anonymous 

You'd have to remove directly from the code all the columns where an error is thrown, i.e., all the columns that are no longer at the source.  Which ones are they exactly? What names?

If you can share the excel files it'd be easier to help.

[Anonymous]

Hi @AlB, here's the 2 files, FROM and TO.

Notice that TO: is exactly the same headers as FROM: except the yellow highlighted columns to the far right are the ones that aren't in the TO: file any longer. Also the sheet name is different (From: Data; To: report1552...)

From file

To file

[AlB · Accepted solution]

@Anonymous 

Like I said before, you'd have to go over the code and remove the references to the columns that do not exist. Or just create a new query loading the new source and  create the steps again. Probably the second option will be faster. In the following code, I remover the references to 'Type Console' column. There are a few left

let
Source = Excel.Workbook(File.Contents("C:\Users\djaggard\Desktop\1.xlsx"), null, true),
report1552420742465_Sheet = Source{[Item="report1552420742465",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(report1552420742465_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Opportunity ID", type text}, {"Opportunity Name", type text}, {"Forecast Category", type text}, {"Service Offering", type text}, {"Account Name", type text}, {"Region", type text}, {"MRCE Currency", type text}, {"MRCE", type number}, {"MRCE (converted) Currency", type text}, {"MRCE (converted)", type number}, {"Weighted MRCE Currency", type text}, {"Weighted MRCE", type number}, {"Weighted MRCE (converted) Currency", type text}, {"Weighted MRCE (converted)", type number}, {"NRC Currency", type text}, {"NRC", type number}, {"NRC (converted) Currency", type text}, {"NRC (converted)", type number}, {"MRC Currency", type text}, {"MRC", type number}, {"MRC (converted) Currency", type text}, {"MRC (converted)", type number}, {"Primary Partner", type any}, {"Type", type text}, {"Lead Source", type text}, {"Created By", type text}, {"Opportunity Owner", type text}, {"Owner Role", type text}, {"Opportunity Owner Email", type text}, {"Close Date", type date}, {"Term (Months)", Int64.Type}, {"Contract Commencement Date", type date}, {"Contract End Date", type date}, {"G2000", Int64.Type}, {"Stage", type text}, {"Account Owner", type text}, {"Account Type", type text}, {"Industry", type text}, {"Parent Account ID", type text}, {"Stage Duration", Int64.Type}, {"Probability (%)", Int64.Type}, {"Age", Int64.Type}, {"Created Date", type date}, {"Fiscal Year", Int64.Type}, {"Fiscal Period", type text}, {"Pipe", type text}, {"Committed", Int64.Type}, {"Forecast", type text}, {"Sales Motion", type text}, {"Bill to?", type text}, {"Ship to?", type text}, {"Partner Name", type text}, {"Push Count", Int64.Type}, {"Last Stage Change Date", type date},  {"Stage_1", type text}, {"Region High", type any}, {"Fcst Exec", type text}, {"ACV", type number}, {"FY&Q", type text}, {"Close Month", Int64.Type}, {"G2000 Flag", type text}, {"Open Pipe?", type text}, {"EMEA Region", type any}, {"Region Consol", type any}, {"Week", type text}, {"Created Date Rule", type text}, {"Stage Change Date Rule", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{ "Region High", "Created Date Rule", "Stage Change Date Rule", "Week", "Region Consol", "EMEA Region", "Open Pipe?", "G2000 Flag", "Close Month", "FY&Q", "ACV", "Fcst Exec", "Stage_1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"MRCE (converted)", "MRCE USD"}, {"NRC (converted)", "NRC USD"}, {"MRC (converted)", "MRC USD"}})
in
#"Renamed Columns"

[Anonymous]

@AlBThanks for this. The issue was that I had retained some of the removed columns in #Changed Type, and also referenced the now deleted #Removed Columns section in the section #Renamed Columns.

I'm not entirely sure how to read the syntax in advanced editor but it seems that for every now row beginning with a # eg. #Changed Type, as soon as you open up the brackets after the Table reference/direction, the # needs to reference the #section prior to that. I presume this is code that says essentially 'take everything in the line above this, and now do this next step'

So for example -- this is how it SHOULD read

#"Promoted Headers" = Table.PromoteHeaders(Data_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"....

#"Removed Columns" = Table.RemoveColums(#Changed Type",{{"....
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"...

And this is how it should NOT read

#"Promoted Headers" = Table.PromoteHeaders(Data_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"....

#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"...