Help - Advanced Editor Issue

[Anonymous]

Hiya, please help i am sending myself mad!!!!

i have a query that has just stopped working and i cannot for the life of me work out why;

let
Source = Excel.Workbook(Web.Contents(""), null, true),
All_Sheet = Source{[Item="All",Kind="Sheet"]}[Data],
#"Promoted Headers1" = Table.PromoteHeaders(All_Sheet, [PromoteAllScalars=true]),
#"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Resourcer", type text}, {"Month", type text}, {"CV", Int64.Type}, {"Interview", Int64.Type}, {"CV Target", Int64.Type}, {"Interview Target", Int64.Type}, {"Placement Target", Int64.Type}, {"Points Target", Int64.Type}, {"Year", Int64.Type}, {"Month & Year", type date}, {"Maximum Value", Int64.Type}, {"CV Maximum Value", type number}, {"Interview Maximum Value", type number}, {"Points Maximum Value", type number}}),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type2",{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Capgemini", Int64.Type}, {"Julia", Int64.Type}, {"Andy", Int64.Type}, {"Marcus", Int64.Type}, {"Helen", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column1", "Month - Vacancies added"}, {"Andy", "Andy Vacancies"}, {"Capgemini", "Cap Vacancies"}, {"Helen", "Helen Vacancies"}, {"Julia", "Julia Vacancies"}, {"Marcus", "Marcus Vacancies"}})
in
#"Renamed Columns"

I keep getting "Expression.Error: The column 'Column1' of the table wasn't found." for the life of me i cannot work out why this is happening?

[parry2k]

@Anonymous if you go thru query steps, can you check if your query works until this step #"Changed Type2" , seems like in next step after #"Changed Type"  is the problem area. Go thru each step and find out where is the issue.

As @Anonymous mentioned may be excel file is changed, seems like extra columns are removed from excel file.

[Anonymous]

@parry2k thanks for responding, it shows all steps unti;l the "changed type". i've looked at the original spreadsheet, nothing has changed at all, it's just not recognising the column1 ref, i've redone the qeury and still unless my coding is wrong them im at a loss, hence why i'm desperately seeking help 

[parry2k]

@Anonymous are you open to share your pbix and excel file to look into it assuming it doesn't contain sensitive data. Feel free to PM me.

[Anonymous]

@parry2k Sadly it does contain sensitive data so i would be unable to share it 😞

I have changed the references from column numbers to the neames, as i feel like its not recognising them, this helped move it on and now its left me with a problem where the prmotoed headers has removed the names of the columns.... Grrrrr

[parry2k]

@Anonymous can you change it to dummy data with few rows and then send it.

[Anonymous]

@parry2k doh!!!! of course yes... lol. I am away tomorrow but will over the weekend. thanks so much for trying to help....

[parry2k]

@Anonymous no worries, whenever you are ready 

[Anonymous]

Hi- I noticed that you're looking at an excell workbook. Check and make sure some smartass didn't change the column name in the excel workbook 

Esp if it workds with these first two lines 

let
Source = Excel.Workbook(Web.Contents(""), null, true),
All_Sheet = Source{[Item="All",Kind="Sheet"]}[Data],

[Anonymous]

Sailkitty,

thanks for the shout, i have checked this and it seems okay, for some reason its all okay till the "changed type" part, i have tried sticking another rename, but again this didnt work. it so odd..... 

Edd