Power Query: DataFormat.Error: We couldn't convert to Number.

Q

Qwest336

Board Regular
Joined
Jun 24, 2015
Messages
53
Good morning all,

I'm at my wit's end! With no detectable changes to the datasource, suddenly I am getting an error on one of my queries. Here's the overview of what happens:

The first import runs as a connection only. No errors are shown. It is an aggregation of 8 files that are pulled into a directory using VBA:

Code: 
let
    Source = Folder.Files("DIRECTORY LISTED HERE"),
    #"Get the files" = Table.SelectRows(Source, each [Extension] = ".csv" or [Extension] = ".txt" or Text.StartsWith([Extension], ".xls")),
    #"Removed Other Columns" = Table.SelectColumns(#"Get the files",{"Content", "Name", "Extension"}),
    #"Invoke Custom Function1" = Table.AddColumn(#"Removed Other Columns", "Transform File from Applicant", each #"Transform File from Applicant"([Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File from Applicant"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Applicant", Table.ColumnNames(#"Transform File from Applicant"(#"Sample File"))),
    #"Removed Other Columns2" = Table.SelectColumns(#"Expanded Table Column1",{"APPLICATION", "INTERVIEW_RESULT"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Other Columns2",{{"APPLICATION", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"APPLICATION", Text.Clean, type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Cleaned Text","DEC","Declined",Replacer.ReplaceValue,{"INTERVIEW_RESULT"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","ES","Not Eligible/Not Suitable",Replacer.ReplaceValue,{"INTERVIEW_RESULT"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","FTR","Fail To Respond",Replacer.ReplaceValue,{"INTERVIEW_RESULT"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","NR","Not Recommended",Replacer.ReplaceValue,{"INTERVIEW_RESULT"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","R","Recommended",Replacer.ReplaceValue,{"INTERVIEW_RESULT"}),
    #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","R-SEL","Recommended-Select",Replacer.ReplaceValue,{"INTERVIEW_RESULT"}),
    #"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","SEL","Select",Replacer.ReplaceValue,{"INTERVIEW_RESULT"}),
    #"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","SNR","Score Not Reached",Replacer.ReplaceValue,{"INTERVIEW_RESULT"}),
    #"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","SR","Strongly Recommended",Replacer.ReplaceValue,{"INTERVIEW_RESULT"}),
    #"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","SR-SEL","Strongly Recommended-Select",Replacer.ReplaceValue,{"INTERVIEW_RESULT"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value9",{{"APPLICATION", Int64.Type}})
in
    #"Changed Type"

The second import similarly runs as a connection only. No errors are shown. It is an aggregation of 8 files that are pulled into a directory using VBA:
Code: 
let
    Source = Folder.Files("DIRECTORY LISTED HERE"),
    #"Get the files" = Table.SelectRows(Source, each [Extension] = ".csv" or [Extension] = ".txt" or Text.StartsWith([Extension], ".xls")),
    #"Removed Other Columns" = Table.SelectColumns(#"Get the files",{"Content", "Name", "Extension"}),
    Trans1 = Table.AddColumn(#"Removed Other Columns", "Custom", each if Text.StartsWith([Extension], ".xls") then Excel.Workbook( [Content]) else null),
    Trans2 = Table.ExpandTableColumn(Trans1, "Custom", {"Name", "Data", "Item", "Kind"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind"}),
    #"The Formula" = Table.AddColumn(Trans2, "Custom", each if [Extension] = ".csv" then 
     Table.PromoteHeaders(Csv.Document([Content])) 
       else if [Extension] = ".txt" 
          then Table.PromoteHeaders(Csv.Document([Content],null,"|" ))
              else if [Custom.Kind] <> "Table" 
                  then Table.PromoteHeaders([Custom.Data]) 
else
[Custom.Data]),
    #"Removed Columns" = Table.RemoveColumns(#"The Formula",{"Content", "Custom.Data"}),
    #"Almost there" = Table.AddColumn(#"Removed Columns", "Custom.1", each Table.RowCount([Custom])),
    MyList = Table.ToList(Table.RemoveColumns(Table.Distinct(Table.ExpandListColumn(Table.AddColumn(#"Almost there", "ColumnHeaders", each Table.ColumnNames([Custom])), "ColumnHeaders"),{"ColumnHeaders"}),{"Name", "Extension", "Custom.Name", "Custom.Item", "Custom.Kind", "Custom", "Custom.1"})),
    #"Here we go" = Table.RenameColumns(#"Almost there",{{"Custom.1", "Total Rows"},{"Custom.Kind", "Kind"}, {"Custom.Item", "Item Name"}, {"Custom.Name", "Sheet Name"}, {"Name", "File Name"}}),
    Expanded = Table.ExpandTableColumn(#"Here we go", "Custom",MyList),
    #"Removed Other Columns1" = Table.SelectColumns(Expanded,{"REQUISITION", "OCC_CODE", "NUM_VACANCIES", "TESTING_REQ", "SECOND_TEST_REQ", "NEED_MVR"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Other Columns1",{{"REQUISITION", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"REQUISITION", Text.Clean, type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Cleaned Text","Y","Yes",Replacer.ReplaceValue,{"TESTING_REQ", "SECOND_TEST_REQ", "NEED_MVR"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","N","No",Replacer.ReplaceValue,{"TESTING_REQ", "SECOND_TEST_REQ", "NEED_MVR"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"REQUISITION", Int64.Type}, {"NUM_VACANCIES", Int64.Type}})
in
    #"Changed Type"

The third import similarly runs as a connection only. No errors are shown, however, I did find an incorrect string in file that was new on the first day of it's import*. It is an aggregation of 4 files that are pulled into a directory using VBA:
Code: 
let
    Source = Folder.Files("DIRECTORY LISTED HERE"),
    #"Get the files" = Table.SelectRows(Source, each [Extension] = ".csv" or [Extension] = ".txt" or Text.StartsWith([Extension], ".xls")),
    #"Removed Other Columns" = Table.SelectColumns(#"Get the files",{"Content", "Name", "Extension"}),
    Trans1 = Table.AddColumn(#"Removed Other Columns", "Custom", each if Text.StartsWith([Extension], ".xls") then Excel.Workbook( [Content]) else null),
    Trans2 = Table.ExpandTableColumn(Trans1, "Custom", {"Name", "Data", "Item", "Kind"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind"}),
    #"The Formula" = Table.AddColumn(Trans2, "Custom", each if [Extension] = ".csv" then 
     Table.PromoteHeaders(Csv.Document([Content])) 
       else if [Extension] = ".txt" 
          then Table.PromoteHeaders(Csv.Document([Content],null,"|" ))
              else if [Custom.Kind] <> "Table" 
                  then Table.PromoteHeaders([Custom.Data]) 
else
[Custom.Data]),
    #"Removed Columns" = Table.RemoveColumns(#"The Formula",{"Content", "Custom.Data"}),
    #"Almost there" = Table.AddColumn(#"Removed Columns", "Custom.1", each Table.RowCount([Custom])),
    MyList = Table.ToList(Table.RemoveColumns(Table.Distinct(Table.ExpandListColumn(Table.AddColumn(#"Almost there", "ColumnHeaders", each Table.ColumnNames([Custom])), "ColumnHeaders"),{"ColumnHeaders"}),{"Name", "Extension", "Custom.Name", "Custom.Item", "Custom.Kind", "Custom", "Custom.1"})),
    #"Here we go" = Table.RenameColumns(#"Almost there",{{"Custom.1", "Total Rows"},{"Custom.Kind", "Kind"}, {"Custom.Item", "Item Name"}, {"Custom.Name", "Sheet Name"}, {"Name", "File Name"}}),
    Expanded = Table.ExpandTableColumn(#"Here we go", "Custom",MyList),
    #"Trimmed Text" = Table.TransformColumns(Expanded,{{"REQUISITION", Text.Trim, type text}, {"JOB_POSTING", Text.Trim, type text}, {"APPLICATION", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"REQUISITION", Text.Clean, type text}, {"JOB_POSTING", Text.Clean, type text}, {"APPLICATION", Text.Clean, type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Cleaned Text",{{"REQUISITION", Int64.Type}, {"JOB_POSTING", Int64.Type}, {"APPLICATION", Int64.Type}, {"NAME", type text}, {"ORG_UNIT", Int64.Type}, {"ORG_DESC", type text}, {"COMPANY", Int64.Type}, {"COMPANY_DESC", type text}, {"BRANCH", type number}, {"BRANCH_DESC", type text}, {"LOCATION", Int64.Type}, {"LOCATION_DESC", type text}, {"POSITION", Int64.Type}, {"POSITION_TITLE", type text}, {"OCC_CODE", type text}, {"OCC_TEXT", type text}, {"OFFER_USRID", type text}, {"OFFER_NAME", type text}, {"OFFER_DT_TM", type datetime}, {"ACCEPT_DECLINE_DT_TM", type datetime}, {"DECLINE_REASON", type text}, {"FTR_DT_TM", type datetime}, {"SUBMIT_DT_TM", type datetime}, {"OFFER_RESCINDED", type date}, {"OHNA_RECEIVE_MED_QUEST", type text}, {"OHNA_USERID", type text}, {"OHNA_USER_NAME", type text}, {"OHNA_RESPONSE_DT_TM", type datetime}, {"RAC", type any}, {"RAC_DECISION", type any}, {"RAC_DECISION_DATE", type any}, {"LS_SUBMITTED_USRID", type text}, {"LS_SUBMITTED_USRNM", type text}, {"LS_SUBMITTED_DT_TM", type datetime}, {"EFFECTIVE_DATE", type date}, {"EMPLOYEE_ID", Int64.Type}, {"CURRENT_POSITION", Int64.Type}, {"CURRENT_POSITION_TITLE", type text}, {"SEPARATION_DATE", Int64.Type}, {"PROBATIONARY_PEROID", type any}, {"SENIORITY_DATE", Int64.Type}, {"RANK", Int64.Type}, {"CANCEL_PRIV_ACCESSION", type any}, {"PRIV_REQUI_NUM", Int64.Type}, {"RE_EMPLOYED_ANNUITANT", type text}, {"RTR_REQ", type text}, {"REHIRE_AFTR_BRK", type text}, {"APPLICANT_DECLINE_OFFER", type any}, {"DATA_TRANSFER_USRID", type text}, {"DATA_TRANSFER_USRNM", type text}, {"DATA_TRANSFER_DT_TM", type datetime}, {"NEW_ORIENT_USRID", type text}, {"NEW_ORIENT_NAME", type text}, {"NEW_ORIENT_DT_TM", type datetime}, {"DELETE_FLAG", type text}}),
    #"Removed Other Columns2" = Table.SelectColumns(#"Changed Type",{"APPLICATION", "FTR_DT_TM", "OHNA_RECEIVE_MED_QUEST", "ACCEPT_DECLINE_DT_TM", "DECLINE_REASON"}),
    #"Extracted Date" = Table.TransformColumns(#"Removed Other Columns2",{{"ACCEPT_DECLINE_DT_TM", DateTime.Date}, {"FTR_DT_TM", DateTime.Date}}),
    #"Replaced Value" = Table.ReplaceValue(#"Extracted Date","Y","Yes",Replacer.ReplaceText,{"OHNA_RECEIVE_MED_QUEST"})
in
    #"Replaced Value"


Here's where it gets sticky. I then merge those connection queries with an ITL of a flat file in the following query:
Code: 
let
    Source = Csv.Document(File.Contents("DIRECTORY LISTED HERE"),[Delimiter="#(tab)", Columns=35, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Replaced Value" = Table.ReplaceValue(#"Promoted Headers","?",null,Replacer.ReplaceValue,{"Requisition #", "Candidate Name", "Candidate Status", "Background Check Request Date", "Background Check Completion Date", "Background Check Type", "GIS Result", "Selective Service Number", "Candidate Selection Date", "Conditional Offer Date", "Candidate Rejection Date", "Candidate Rejection Reason", "CandidateID", "CandidateEffectiveDate", "FormerPostalFlag", "SupportingDocumentFlag", "PostalContractFlag", "SSARegistrationRequiredFlag", "RegisteredSSAFlag", "SSAExemptionReason", "ServiceCharacter", "CandidacyID", "ApplicationID", "FirstName", "LastName", "RequisitionEffectiveDate", "RequisitionCreateDate", "RequisitionTitle", "Candidates on Hiring List", "Hiring List Create Date", "Position On Hiring List", "CompanyTxt", "AreaName", "OrganizationName"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Y ","Yes",Replacer.ReplaceValue,{"SSARegistrationRequiredFlag"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","N ","No",Replacer.ReplaceText,{"SSARegistrationRequiredFlag"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","N","No",Replacer.ReplaceValue,{"FormerPostalFlag", "SupportingDocumentFlag", "PostalContractFlag", "RegisteredSSAFlag"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","Y","Yes",Replacer.ReplaceValue,{"FormerPostalFlag", "SupportingDocumentFlag", "PostalContractFlag", "RegisteredSSAFlag"}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Replaced Value4",{{"Candidate Name", Text.Proper}}),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Capitalized Each Word", "Text Before Delimiter", each Text.BeforeDelimiter([InternalJobTitle], " ", 0), type text),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Text Before Delimiter",{{"Text Before Delimiter", "Occ-Code"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"InternalJobTitle"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Background Check Request Date", type date}, {"Background Check Completion Date", type date}, {"Candidate Selection Date", type date}, {"Conditional Offer Date", type date}, {"Candidate Rejection Date", type date}, {"CandidateEffectiveDate", type date}, {"RequisitionEffectiveDate", type date}, {"RequisitionCreateDate", type date}, {"Hiring List Create Date", type date}, {"Requisition #", Int64.Type}, {"Selective Service Number", Int64.Type}, {"CandidateID", Int64.Type}, {"CandidacyID", Int64.Type}, {"ApplicationID", Int64.Type}, {"Candidates on Hiring List", Int64.Type}, {"Position On Hiring List", Int64.Type}}),
    #"Merged Queries1" = Table.NestedJoin(#"Changed Type",{"ApplicationID"},Applicant,{"APPLICATION"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn1" = Table.ExpandTableColumn(#"Merged Queries1", "NewColumn", {"INTERVIEW_RESULT"}, {"INTERVIEW_RESULT"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded NewColumn1",{"Requisition #"},Requisition,{"REQUISITION"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"OCC_CODE", "NUM_VACANCIES", "TESTING_REQ", "SECOND_TEST_REQ", "NEED_MVR"}, {"OCC_CODE", "NUM_VACANCIES", "TESTING_REQ", "SECOND_TEST_REQ", "NEED_MVR"}),
    #"Merged Queries2" = Table.NestedJoin(#"Expanded NewColumn",{"ApplicationID"},#"New Hire Tracking (1098)",{"APPLICATION"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn2" = Table.ExpandTableColumn(#"Merged Queries2", "NewColumn", {"FTR_DT_TM", "OHNA_RECEIVE_MED_QUEST", "ACCEPT_DECLINE_DT_TM", "DECLINE_REASON"}, {"FTR_DT_TM", "OHNA_RECEIVE_MED_QUEST", "ACCEPT_DECLINE_DT_TM", "DECLINE_REASON"}),
    #"Merged Queries3" = Table.NestedJoin(#"Expanded NewColumn2",{"CandidateID"},#"Completed Cases Generic RP",{"CandidateID"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn3" = Table.ExpandTableColumn(#"Merged Queries3", "NewColumn", {"AdmittedCriminalInformation", "CustomerGradingNotes", "GradingRuleResults", "AdverseActionLetterSent"}, {"AdmittedCriminalInformation", "CustomerGradingNotes", "GradingRuleResults", "AdverseActionLetterSent"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded NewColumn3",{{"INTERVIEW_RESULT", "Interview Result"}}),
    #"Added Conditional Column3" = Table.AddColumn(#"Renamed Columns", "MVR Request Date", each if [Background Check Type] = "Motor Vehicle Record" then [Background Check Request Date] else null ),
    #"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "MVR Completion Date", each if [Background Check Type] = "Motor Vehicle Record" then [Background Check Completion Date] else null ),
    #"Added Conditional Column5" = Table.AddColumn(#"Added Conditional Column4", "Criminal Request Date", each if [Background Check Type] = "Criminal Check" then [Background Check Request Date] else null ),
    #"Added Conditional Column6" = Table.AddColumn(#"Added Conditional Column5", "Criminal Completion Date", each if [Background Check Type] = "Criminal Check" then [Background Check Completion Date] else null ),
    #"Added Conditional Column7" = Table.AddColumn(#"Added Conditional Column6", "Drug Test Request Date", each if [Background Check Type] = "Drug Test" then [Background Check Request Date] else null ),
    #"Added Conditional Column8" = Table.AddColumn(#"Added Conditional Column7", "Drug Test Completion Date", each if [Background Check Type] = "Drug Test" then [Background Check Completion Date] else null ),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column8", "MVR Results", each if [Background Check Type] = "Motor Vehicle Record" then [GIS Result] else null ),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Criminal Check Results", each if [Background Check Type] = "Criminal Check" then [GIS Result] else null ),
    #"Added Conditional Column" = Table.AddColumn(#"Added Conditional Column2", "Drug Test Results", each if [Background Check Type] = "Drug Test" then [GIS Result] else null )
in
    #"Added Conditional Column"



I then use the following query to load to a worksheet after it performs a grouping and some additional transforms:

Code: 
let
    Source = THIS IS THE PREVIOUS QUERY NAME ABOVE,
    #"Grouped Rows" = Table.Group(Source, {"ApplicationID"}, {{"FillUp", each Table.FirstN(Table.FillUp(_,Table.ColumnNames(_)), 1), type table}}),
    #"Expanded FillUp" = Table.ExpandTableColumn(#"Grouped Rows", "FillUp", {"Requisition #", "Candidate Name", "Candidate Status", "Background Check Request Date", "Background Check Completion Date", "Background Check Type", "GIS Result", "Selective Service Number", "Candidate Selection Date", "Conditional Offer Date", "Candidate Rejection Date", "Candidate Rejection Reason", "CandidateID", "CandidateEffectiveDate", "FormerPostalFlag", "SupportingDocumentFlag", "PostalContractFlag", "SSARegistrationRequiredFlag", "RegisteredSSAFlag", "SSAExemptionReason", "ServiceCharacter", "CandidacyID", "ApplicationID", "FirstName", "LastName", "RequisitionEffectiveDate", "RequisitionCreateDate", "RequisitionTitle", "Candidates on Hiring List", "Hiring List Create Date", "Position On Hiring List", "CompanyTxt", "AreaName", "OrganizationName", "Occ-Code", "Interview Result", "OCC_CODE", "NUM_VACANCIES", "TESTING_REQ", "SECOND_TEST_REQ", "NEED_MVR", "FTR_DT_TM", "OHNA_RECEIVE_MED_QUEST", "ACCEPT_DECLINE_DT_TM", "DECLINE_REASON", "AdmittedCriminalInformation", "CustomerGradingNotes", "GradingRuleResults", "AdverseActionLetterSent", "MVR Request Date", "MVR Completion Date", "Criminal Request Date", "Criminal Completion Date", "Drug Test Request Date", "Drug Test Completion Date", "MVR Results", "Criminal Check Results", "Drug Test Results"}, {"Requisition #", "Candidate Name", "Candidate Status", "Background Check Request Date", "Background Check Completion Date", "Background Check Type", "GIS Result", "Selective Service Number", "Candidate Selection Date", "Conditional Offer Date", "Candidate Rejection Date", "Candidate Rejection Reason", "CandidateID", "CandidateEffectiveDate", "FormerPostalFlag", "SupportingDocumentFlag", "PostalContractFlag", "SSARegistrationRequiredFlag", "RegisteredSSAFlag", "SSAExemptionReason", "ServiceCharacter", "CandidacyID", "ApplicationID.1", "FirstName", "LastName", "RequisitionEffectiveDate", "RequisitionCreateDate", "RequisitionTitle", "Candidates on Hiring List", "Hiring List Create Date", "Position On Hiring List", "CompanyTxt", "AreaName", "OrganizationName", "Occ-Code", "Interview Result", "OCC_CODE", "NUM_VACANCIES", "TESTING_REQ", "SECOND_TEST_REQ", "NEED_MVR", "FTR_DT_TM", "OHNA_RECEIVE_MED_QUEST", "ACCEPT_DECLINE_DT_TM", "DECLINE_REASON", "AdmittedCriminalInformation", "CustomerGradingNotes", "GradingRuleResults", "AdverseActionLetterSent", "MVR Request Date", "MVR Completion Date", "Criminal Request Date", "Criminal Completion Date", "Drug Test Request Date", "Drug Test Completion Date", "MVR Results", "Criminal Check Results", "Drug Test Results"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded FillUp",{{"Background Check Request Date", type date}, {"Background Check Completion Date", type date}, {"Candidate Selection Date", type date}, {"Conditional Offer Date", type date}, {"Candidate Rejection Date", type date}, {"CandidateEffectiveDate", type date}, {"RequisitionEffectiveDate", type date}, {"RequisitionCreateDate", type date}, {"Hiring List Create Date", type date}, {"ACCEPT_DECLINE_DT_TM", type date}, {"MVR Request Date", type date}, {"MVR Completion Date", type date}, {"Criminal Request Date", type date}, {"Criminal Completion Date", type date}, {"Drug Test Request Date", type date}, {"Drug Test Completion Date", type date}, {"AdverseActionLetterSent", type date}, {"FTR_DT_TM", type date}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","Space(s)","Pending",Replacer.ReplaceValue,{"MVR Results", "Criminal Check Results", "Drug Test Results"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Background Check Request Date", "Background Check Completion Date", "Background Check Type", "GIS Result"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Removed Columns","/","-",Replacer.ReplaceText,{"OrganizationName"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Replaced Value1",{"Requisition #", "Candidate Name", "Candidate Status", "MVR Results", "Criminal Check Results", "Drug Test Results", "Drug Test Completion Date", "Drug Test Request Date", "Criminal Completion Date", "Criminal Request Date", "MVR Completion Date", "MVR Request Date", "Selective Service Number", "Candidate Selection Date", "Conditional Offer Date", "Candidate Rejection Date", "Candidate Rejection Reason", "CandidateID", "CandidateEffectiveDate", "FormerPostalFlag", "SupportingDocumentFlag", "PostalContractFlag", "SSARegistrationRequiredFlag", "RegisteredSSAFlag", "SSAExemptionReason", "ServiceCharacter", "CandidacyID", "ApplicationID", "ApplicationID.1", "FirstName", "LastName", "RequisitionEffectiveDate", "RequisitionCreateDate", "RequisitionTitle", "Candidates on Hiring List", "Hiring List Create Date", "Position On Hiring List", "CompanyTxt", "AreaName", "OrganizationName", "Occ-Code", "Interview Result", "OCC_CODE", "NUM_VACANCIES", "TESTING_REQ", "SECOND_TEST_REQ", "NEED_MVR", "FTR_DT_TM", "OHNA_RECEIVE_MED_QUEST", "ACCEPT_DECLINE_DT_TM", "DECLINE_REASON", "AdmittedCriminalInformation", "CustomerGradingNotes", "GradingRuleResults", "AdverseActionLetterSent"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"ApplicationID.1"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns1",{"Requisition #", "Candidate Name", "Interview Result", "Candidate Status", "MVR Results", "Criminal Check Results", "Drug Test Results", "Drug Test Completion Date", "Drug Test Request Date", "Criminal Completion Date", "Criminal Request Date", "MVR Completion Date", "MVR Request Date", "Selective Service Number", "Candidate Selection Date", "Conditional Offer Date", "Candidate Rejection Date", "Candidate Rejection Reason", "CandidateID", "CandidateEffectiveDate", "FormerPostalFlag", "SupportingDocumentFlag", "PostalContractFlag", "SSARegistrationRequiredFlag", "RegisteredSSAFlag", "SSAExemptionReason", "ServiceCharacter", "CandidacyID", "ApplicationID", "FirstName", "LastName", "RequisitionEffectiveDate", "RequisitionCreateDate", "RequisitionTitle", "Candidates on Hiring List", "Hiring List Create Date", "Position On Hiring List", "CompanyTxt", "AreaName", "OrganizationName", "Occ-Code", "OCC_CODE", "NUM_VACANCIES", "TESTING_REQ", "SECOND_TEST_REQ", "NEED_MVR", "FTR_DT_TM", "OHNA_RECEIVE_MED_QUEST", "ACCEPT_DECLINE_DT_TM", "DECLINE_REASON", "AdmittedCriminalInformation", "CustomerGradingNotes", "GradingRuleResults", "AdverseActionLetterSent"}),
    #"Added Custom" = Table.AddColumn(#"Reordered Columns1", "Crim - Drug Result", each [Criminal Check Results] & " - " & [Drug Test Results]),
    #"Reordered Columns2" = Table.ReorderColumns(#"Added Custom",{"Requisition #", "Candidate Name", "Interview Result", "Candidate Status", "MVR Results", "Criminal Check Results", "Drug Test Results", "FTR_DT_TM", "Candidate Rejection Date", "Candidate Rejection Reason", "MVR Request Date", "MVR Completion Date", "Criminal Request Date", "Criminal Completion Date", "Drug Test Request Date", "Drug Test Completion Date", "Selective Service Number", "Candidate Selection Date", "Conditional Offer Date", "CandidateID", "CandidateEffectiveDate", "FormerPostalFlag", "SupportingDocumentFlag", "PostalContractFlag", "SSARegistrationRequiredFlag", "RegisteredSSAFlag", "SSAExemptionReason", "ServiceCharacter", "CandidacyID", "ApplicationID", "FirstName", "LastName", "RequisitionEffectiveDate", "RequisitionCreateDate", "RequisitionTitle", "Candidates on Hiring List", "Hiring List Create Date", "Position On Hiring List", "CompanyTxt", "AreaName", "OrganizationName", "Occ-Code", "OCC_CODE", "NUM_VACANCIES", "TESTING_REQ", "SECOND_TEST_REQ", "NEED_MVR", "OHNA_RECEIVE_MED_QUEST", "ACCEPT_DECLINE_DT_TM", "DECLINE_REASON", "AdmittedCriminalInformation", "CustomerGradingNotes", "GradingRuleResults", "AdverseActionLetterSent", "Crim - Drug Result"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns2",{{"Crim - Drug Result", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "TESTING REQ", each if [#"Occ-Code"] = "2325-0002" then "No" else if [#"Occ-Code"] = "5201-1001" then "No" else if [#"Occ-Code"] = "2305-0115" then "No" else if [#"Occ-Code"] = "2305-0148" then "No" else if [#"Occ-Code"] = "5201-0008" then "No" else if [#"Occ-Code"] = "2310-7145" then "No" else if [#"Occ-Code"] = "2320-7006" then "No" else if [#"Occ-Code"] = "2315-0088" then "No" else "Yes" ),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column", "NEED MVR", each if [#"Occ-Code"] = "2310-0045" then "Yes" else if [#"Occ-Code"] = "2310-0046" then "Yes" else if [#"Occ-Code"] = "2325-07XX" then "Yes" else if [#"Occ-Code"] = "2325-0002" then "Yes" else if [#"Occ-Code"] = "2395-0033" then "Yes" else if [#"Occ-Code"] = "2310-0032" then "Yes" else if [#"Occ-Code"] = "5703-0005" then "Yes" else if [#"Occ-Code"] = "2395-0028" then "Yes" else "Verify Action" ),
    #"Reordered Columns3" = Table.ReorderColumns(#"Added Conditional Column2",{"Requisition #", "Candidate Name", "Interview Result", "Candidate Status", "MVR Results", "Criminal Check Results", "Drug Test Results", "FTR_DT_TM", "Candidate Rejection Date", "Candidate Rejection Reason", "MVR Request Date", "MVR Completion Date", "Criminal Request Date", "Criminal Completion Date", "Drug Test Request Date", "Drug Test Completion Date", "Selective Service Number", "Candidate Selection Date", "Conditional Offer Date", "CandidateID", "CandidateEffectiveDate", "FormerPostalFlag", "SupportingDocumentFlag", "PostalContractFlag", "SSARegistrationRequiredFlag", "RegisteredSSAFlag", "SSAExemptionReason", "ServiceCharacter", "CandidacyID", "ApplicationID", "FirstName", "LastName", "RequisitionEffectiveDate", "RequisitionCreateDate", "RequisitionTitle", "Candidates on Hiring List", "Hiring List Create Date", "Position On Hiring List", "CompanyTxt", "AreaName", "OrganizationName", "Occ-Code", "OCC_CODE", "NUM_VACANCIES", "TESTING_REQ", "TESTING REQ", "SECOND_TEST_REQ", "NEED_MVR", "NEED MVR", "OHNA_RECEIVE_MED_QUEST", "ACCEPT_DECLINE_DT_TM", "DECLINE_REASON", "AdmittedCriminalInformation", "CustomerGradingNotes", "GradingRuleResults", "AdverseActionLetterSent", "Crim - Drug Result"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns3",{"TESTING_REQ", "NEED_MVR"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"NEED MVR", "NEED_MVR"}, {"TESTING REQ", "TESTING_REQ"}})in
    #"Renamed Columns"

On the "Grouped Rows" transform is where I get the following error:

Code: 
DataFormat.Error: We couldn't convert to Number.
Details:
    Selected

Things I have tried:
1. I've checked all of the source files in the connection queries for bad data. I didn't think this was the issue because the individual queries run. I removed the one instance I found which appeared to be created by a repeated text qualifier on the export.

2. I've inserted Trim & Clean tranforms on the field I use to join to ensure there are aren't any problematic characters. I didn't think there was because I had already converted to a Number and didn't get any errors.

3. I've rebuilt each Merged query join in the fourth query above.

Notes: The "Source" line of the last query shows the correct output of the previous query. Everything seems to be running fine up until that point.

I need your help, all! Sorry if I gave too much but I am unsure what you might need.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
Good afternoon all,

After significant digging (and a little bit of luck), I was able to find an entry in two of the 4 files that, due to a Hashtag (a Pound sign for all of us old heads) entered in her last name, caused a line break before the import process. After I fixed those entries in both files, it started working again!

I'm currently trying to figure out a way to handle that in the import process.
 
Upvote 0
I had a similar issue, but needed change a double line feed into a single line feed while reading in a CSV file. I fixed it by reading the file in as binary, doing a replace on the linefeed, then converting it back to a normal table.
You could probably do something similar by replacing the #'s as you read the data file, unless you need them for something
Code was:
Code: 
Source = Text.Replace(Text.FromBinary(File.Contents("\\weekly financials\RAW.csv")), "#(lf)#(lf)","#(lf)"),
Source2 = Text.Replace(Source, "#(lf)""",""""),
Continue = Lines.FromText(Source2),
#"Converted to Table" = Table.FromList(Continue, Splitter.SplitTextByDelimiter(","), null, null, ExtraValues.Error),
#"Promoted Headers" = Table.PromoteHeaders(#"Converted to Table", [PromoteAllScalars=true]),
 
Last edited:
Upvote 0
horseyride said:
I had a similar issue, but needed change a double line feed into a single line feed while reading in a CSV file. I fixed it by reading the file in as binary, doing a replace on the linefeed, then converting it back to a normal table.
You could probably do something similar by replacing the #'s as you read the data file, unless you need them for something
Code was:
Code: 
Source = Text.Replace(Text.FromBinary(File.Contents("\\weekly financials\RAW.csv")), "#(lf)#(lf)","#(lf)"),
Source2 = Text.Replace(Source, "#(lf)""",""""),
Continue = Lines.FromText(Source2),
#"Converted to Table" = Table.FromList(Continue, Splitter.SplitTextByDelimiter(","), null, null, ExtraValues.Error),
#"Promoted Headers" = Table.PromoteHeaders(#"Converted to Table", [PromoteAllScalars=true]),
Click to expand...

That's a GREAT idea. I spent most of yesterday failing at finding an option to fix it. Let me see if I can work that into the process.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
adding extra lines to query removed all steps
Replies
8
Views
374
RoryA
RoryA
P
  • Question Question
Power Query: download did not complete -> [DataFormat.Error] We couldn't convert to Number
Replies
0
Views
4K
pacx92
P
M
  • Solved
Power Query use column numbers instead of names for row actions
Replies
4
Views
695
JGordon11
JGordon11
ExcelAtEverything
  • Solved
Fill In Missing Dates in Power Query
Replies
2
Views
5K
ExcelAtEverything
ExcelAtEverything
ExcelAtEverything
  • Solved
Power Query - Multiply column contents if row "Date" is before specified date
Replies
1
Views
4K
ExcelAtEverything
ExcelAtEverything

Forum statistics

Threads
1,223,885
Messages
6,175,179
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top