I have been refreshing the data files in the workbook on a weekly basis for months with no issues until now. Today, the function stops working and I am getting an error when I replace the files which are exported from our system each week.
I've checked through last week and this week files and I see not changes in the layout or naming convention of the exported data. (ie. no change to row/header layout; no columns added/deleted; same naming convention, etc.)
I've checked the function and there are no scripting errors. The function removes top row; and promotes row2 as header. See below screenshot for a copy of the script.
I don't understand why function has stopped working on system files from one week to the next. I have no idea on how to troubleshoot this. Any advice appreciated.
= (filepath)=>
let
Source = Excel.Workbook(File.Contents(filepath)),
sheet1_Sheet = Source{[Item="sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type text}, {"Column5", type any}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type any}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type any}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Career", type text}, {"Career Nbr", Int64.Type}, {"Appl Nbr", Int64.Type}, {"Prog Nbr", Int64.Type}, {"Acad Prog", type text}, {"AreaOfSpecialization", type text}, {"Option", type text}, {"Prog Actn", type text}, {"Action Rsn", Int64.Type}, {"Admit Term", Int64.Type}, {"Prefix", type text}, {"Last", type text}, {"First Name", type text}, {"Sex", type text}, {"Birthdate", Int64.Type}, {"HAddr1", type text}, {"HAddr2", type text}, {"HAddr3", type text}, {"HAddr4", type text}, {"HCity", type text}, {"HProv", type text}, {"HPostalCd", type text}, {"HCountry", type text}, {"MAddr1", type text}, {"MAddr2", type text}, {"MAddr3", type text}, {"MAddr4", type text}, {"MCity", type text}, {"MState", type text}, {"MPostalCd", type text}, {"MCountry", type text}, {"ResEntryDate", Int64.Type}, {"ResStatus", type text}, {"ResCountry", type text}, {"PrefPhone", type text}, {"CellPhone", type text}, {"Email", type text}, {"Email_1", type text}, {"Admit Type", type text}, {"Choice", Int64.Type}})
in
#"Changed Type1"
I've checked through last week and this week files and I see not changes in the layout or naming convention of the exported data. (ie. no change to row/header layout; no columns added/deleted; same naming convention, etc.)
I've checked the function and there are no scripting errors. The function removes top row; and promotes row2 as header. See below screenshot for a copy of the script.
I don't understand why function has stopped working on system files from one week to the next. I have no idea on how to troubleshoot this. Any advice appreciated.
= (filepath)=>
let
Source = Excel.Workbook(File.Contents(filepath)),
sheet1_Sheet = Source{[Item="sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type text}, {"Column5", type any}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type any}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type any}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Career", type text}, {"Career Nbr", Int64.Type}, {"Appl Nbr", Int64.Type}, {"Prog Nbr", Int64.Type}, {"Acad Prog", type text}, {"AreaOfSpecialization", type text}, {"Option", type text}, {"Prog Actn", type text}, {"Action Rsn", Int64.Type}, {"Admit Term", Int64.Type}, {"Prefix", type text}, {"Last", type text}, {"First Name", type text}, {"Sex", type text}, {"Birthdate", Int64.Type}, {"HAddr1", type text}, {"HAddr2", type text}, {"HAddr3", type text}, {"HAddr4", type text}, {"HCity", type text}, {"HProv", type text}, {"HPostalCd", type text}, {"HCountry", type text}, {"MAddr1", type text}, {"MAddr2", type text}, {"MAddr3", type text}, {"MAddr4", type text}, {"MCity", type text}, {"MState", type text}, {"MPostalCd", type text}, {"MCountry", type text}, {"ResEntryDate", Int64.Type}, {"ResStatus", type text}, {"ResCountry", type text}, {"PrefPhone", type text}, {"CellPhone", type text}, {"Email", type text}, {"Email_1", type text}, {"Admit Type", type text}, {"Choice", Int64.Type}})
in
#"Changed Type1"