Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Changing data source to source with slightly different content

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"

 

Need Help
  • AlB's avatar
    AlB
    7 years ago

    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"

     

4 Replies

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        AlB
        Community Champion
        7 years ago

        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"