Error - DataFormat.Error: We couldn't convert to Number. Details J7510

bukhari1979 Unfortunately, when I applied these steps it replaced all the values with the default value for errors found. My Power BI goes from Data format error. We couldnt convert to number to sometimes saying Excel file is corrupt. 

Here is my Advanced Editor. Basically the Column called "Location ID" starts off as a alpha numeric value. I replace the alphas with blank or a number value then change formatting to number so i can tie the number to another table needed. Any assistance is greatly appreciated. What's odd is I im importing two excel files one loads fine the newer one is the one throwing errors. I cannot tell any difference in the Excel docs regarding naming or formats that would be the cause.

let
Source = SharePoint.Files("https://companyname.sharepoint.com/sites/talentanalytic/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https://companyname.sharepoint.com/sites/talentanalytic/Shared Documents/Cornerstone/Source Reports/ALL Trainings/")),
#"Invoke Custom Function2" = Table.AddColumn(#"Filtered Rows", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns2" = Table.RenameColumns(#"Invoke Custom Function2", {"Name", "Source.Name"}),
#"Removed Other Columns2" = Table.SelectColumns(#"Renamed Columns2", {"Source.Name", "Transform File"}),
#"Expanded Table Column2" = Table.ExpandTableColumn(#"Removed Other Columns2", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File (3)"))),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Table Column2","AAA-","",Replacer.ReplaceText,{"Location ID"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","INS-MNSC","1111",Replacer.ReplaceText,{"Location ID"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","COM","2222",Replacer.ReplaceText,{"Location ID"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Arizona_Remote","3333",Replacer.ReplaceText,{"Location ID"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","Company_Company_Company","4444",Replacer.ReplaceText,{"Location ID"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","Ontario_Remote","5555",Replacer.ReplaceText,{"Location ID"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","Texas_Remote","6666",Replacer.ReplaceText,{"Location ID"}),
#"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","Utah_Remote","7777",Replacer.ReplaceText,{"Location ID"}),
#"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","SC_MN","8888",Replacer.ReplaceText,{"Location ID"}),
#"Trimmed Text" = Table.TransformColumns(#"Replaced Value8",{{"Location ID", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Location ID", Text.Clean, type text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Cleaned Text",{{"Location ID", Int64.Type}}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"Location ID", 9999}}),
#"Added Conditional Column" = Table.AddColumn(#"Replaced Errors", "Status", each if [Transcript Status] = "Completed" then "Completed" else if [Transcript Status] = "Completed (Equivalent)" then "Completed" else if [Transcript Status] = "Exempt" then "Completed" else if [Transcript Status] = "In Progress" then "In Progress" else if [Transcript Status] = "Registered" then "In Progress" else if [Transcript Status] = "In Progress / Past Due" then "Past Due" else if [Transcript Status] = "Registered / Past Due" then "Past Due" else "Unknown"),
#"Extracted Date" = Table.TransformColumns(#"Added Conditional Column",{})
in
#"Extracted Date"

Hi bukhari1979  sorry your solution is misleading and not correct.