Phantom field removal!

[Jossy]

Hi,

I've just tried to refresh a report and it's erroring because it can't find the "Notes_1" field. The data source has never had a "Notes_1" field - only a "Notes" field - guess something has gone wrong in a previous refresh.

My issue is that I can't work out how to get the refresh back on track. I've deleted the field from the report and have checked that it doesn't appear in any of the data tables, however I still get the same error!

Any suggestions guys?

Thanks,

Jossy

[Greg_Deckler]

Check your query using Edit Query. Sometimes if you add steps, etc, it messes up subsequent steps and you get weird things going on. Check your Advanced View in your query, copy and paste to Notepad or something and do a find for Notes_1.

[Greg_Deckler]

@Jossy - Couldn't reply to your response, I think because the M code messes up Lithium if not posted in code block, but copy and paste and backup your query then select all and try pasting in this version:

let
   Source = Excel.Workbook(File.Contents("C:\Users\Philip.Joss\OneDrive for Business\Clients (Current)\Iglo\Iglo spend&opp analysis v4.xlsx"), null, true),
   Projects_Sheet = Source{[Item="Projects",Kind="Sheet"]}[Data],
   #"Promoted Headers" = Table.PromoteHeaders(Projects_Sheet),
   #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Category", type text}, {"Sub-category", type text}, {"PRISM ID", Int64.Type}, {"Project name", type text}, {"Notes", type text}, {"Site", type text}, {"Spend", type number}, {"Addressable spend %", type number}, {"Addressable spend €", type number}, {"Typical annualised savings % (LOW)#(lf)", type number}, {"Typical annualised savings % (HIGH)", type number}, {"Annualised savings € (LOW)", type number}, {"Annualised savings € (HIGH)", type number}, {"How confident are we the HIGH savings are accessible? Don't consider feasibility/ business support/time.", type number}, {"Annualised savings € (RISKED 1)", type number}, {"How feasible is the project and/or how likely is the project to be signed off by the business?", type number}, {"Annualised savings € (RISKED 2)", type number}, {"2016 start (month)", Int64.Type}, {"In-year savings € (RISKED 2)", type number}, {"How confident are we of the timelines? Don't consider stakeholder support - that should be a given for the purposes of this risk.", type number}, {"In-year savings € (RISKED 3)", type number}, {"FTE", type number}, {"Hours per week", type number}, {"Duration (months)", Int64.Type}, {"Annualised FTE equivilent", type number}, {"Annualised FTE equivilent (Stakeholder risk)", type number}, {"Company", type text}}),
   #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Annualised savings € (RISKED 1)", "FY savings (Max potential)"}, {"Annualised savings € (RISKED 2)", "FY savings (Risked)"}, {"In-year savings € (RISKED 2)", "In-year savings (Risked)"}, {"In-year savings € (RISKED 3)", "In-year savings (Timeline risked)"}, {"Sub-category", "Category 2"}, {"Category", "Category 1"}})
in
   #"Renamed Columns"

[Jossy]

That looks exactly what it is! I've tried various combinations of removing the offending 'code' but I keep getting differing errors. Could you help?

let

   Source = Excel.Workbook(File.Contents("C:\Users\Philip.Joss\OneDrive for Business\Clients (Current)\Iglo\Iglo spend&opp analysis v4.xlsx"), null, true),

   Projects_Sheet = Source{[Item="Projects",Kind="Sheet"]}[Data],

   #"Promoted Headers" = Table.PromoteHeaders(Projects_Sheet),

   #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Category", type text}, {"Sub-category", type text}, {"PRISM ID", Int64.Type}, {"Project name", type text}, {"Notes", type text}, {"Site", type text}, {"Spend", type number}, {"Addressable spend %", type number}, {"Addressable spend €", type number}, {"Typical annualised savings % (LOW)#(lf)", type number}, {"Typical annualised savings % (HIGH)", type number}, {"Annualised savings € (LOW)", type number}, {"Annualised savings € (HIGH)", type number}, {"How confident are we the HIGH savings are accessible? Don't consider feasibility/ business support/time.", type number}, {"Annualised savings € (RISKED 1)", type number}, {"How feasible is the project and/or how likely is the project to be signed off by the business?", type number}, {"Annualised savings € (RISKED 2)", type number}, {"2016 start (month)", Int64.Type}, {"In-year savings € (RISKED 2)", type number}, {"How confident are we of the timelines? Don't consider stakeholder support - that should be a given for the purposes of this risk.", type number}, {"In-year savings € (RISKED 3)", type number}, {"FTE", type number}, {"Hours per week", type number}, {"Duration (months)", Int64.Type}, {"Annualised FTE equivilent", type number}, {"Annualised FTE equivilent (Stakeholder risk)", type number}, {"Company", type text}, {"Notes_1", type text}}),

   #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Annualised savings € (RISKED 1)", "FY savings (Max potential)"}, {"Annualised savings € (RISKED 2)", "FY savings (Risked)"}, {"In-year savings € (RISKED 2)", "In-year savings (Risked)"}, {"In-year savings € (RISKED 3)", "In-year savings (Timeline risked)"}, {"Sub-category", "Category 2"}, {"Category", "Category 1"}}),

   #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Notes_1"})

in

   #"Removed Columns"