let
Source = LossRunToExcel,
#"Detected Type Mismatches" = let
tableWithOnlyPrimitiveTypes = Table.SelectColumns(Source, Table.ColumnsOfType(Source, {type nullable number, type nullable text, type nullable logical, type nullable date, type nullable datetime, type nullable datetimezone, type nullable time, type nullable duration})),
recordTypeFields = Type.RecordFields(Type.TableRow(Value.Type(tableWithOnlyPrimitiveTypes))),
fieldNames = Record.FieldNames(recordTypeFields),
fieldTypes = List.Transform(Record.ToList(recordTypeFields), each [Type]),
pairs = List.Transform(List.Positions(fieldNames), (i) => {fieldNames{i}, (v) => if v = null or Value.Is(v, fieldTypes{i}) then v else error [Message = "The type of the value does not match the type of the column.", Detail = v], fieldTypes{i}})
in
Table.TransformColumns(Source, pairs),
#"Added Index" = Table.AddIndexColumn(#"Detected Type Mismatches", "Row Number" ,1),
#"Kept Errors" = Table.SelectRowsWithErrors(#"Added Index", {"Insured Name", "AsOf", "Policy Number", "Policy Inception Date", "Policy Expiration Date", "Insurer", "Claim Number", "Claimant First Name", "Claimant Middle Name", "Claimant Last Name", "Loss Date", "Claim Status", "Claim Type", "Risk State", "Jurisdiction", "Gender", "Claimant Birth Date", "Claimant Age", "Claimant Hire Date", "Years Employed", "Occupation Desc", "Average Weekly Wage", "Class Code", "Closed Date", "Reopened Date", "Litigated", "Date Reported To Insurer", "Days To Report", "Date Employer Informed", "Reporting Lag", "Claims Professional", "Reporting Location Desc", "Reporting Location Address1", "Reporting Location Address2", "Reporting Location City", "Reporting Location State", "Reporting Location Zip Code", "Injury Location Desc", "Injury Location Address1", "Injury Location Address2", "Injury Location City", "Injury Location State", "Injury Location Zip Code", "Reporting Group OrgID", "Reporting Group Code Path", "Reporting Group 1", "Reporting Group 1 Code", "Reporting Group 2", "Reporting Group 2 Code", "Employee ID", "Injury Cause Desc", "Nature Of Injury Desc", "Body Part Desc", "How Injury Occurred", "Injury Desc", "Activity", "Work Start Time", "Time of Incident", "PD Rating", "Medical Incurred", "Medical Paid", "TD Incurred", "TD Paid", "PD Incurred", "PD Paid", "VR Incurred", "VR Paid", "Expense Incurred", "Expense Paid", "Total Gross Incurred", "Total Outstanding Reserve", "Total Gross Paid", "Total Recoveries", "Third Party Recoveries", "Deductible Reimbursed", "Deductible Amount", "Deductible Type", "Day", "Year", "Month", "Month of Loss Numeric", "Employee Lag", "Lagtime Reporting", "Lagtime Banding", "LOE by Day", "Reporting Location", "Incident Location", "Claimant Birth Date Text", "Claimant Name Combined", "Age Range", "LOE By Year", "LOE Range", "LOE by Year Rounded", "LOE Label1", "LOE Label2", "LOE Label2 Sort", "Time Started", "Time Injured", "Time at Work", "Time to Injury Group", "Employer Lag Range", "Overall Lag Reporting", "Overall Lag Range ", "Shift Range", "Day Order", "Employee Lag Range Days", "Employer Lag Range Days", "Policy Inception Date Text", "Employee Lag Range Days Sort", "Employer Lag Range Days Sort", "Claimant Birth Date Text and Dash", "Merged Claimant Name and Birth Date", "Merged Injury Location No Zip Code", "Injury Location Merged", "Shift", "Shift Order", "24 Hour Round", "Claim Type All", "Claim Type First Aid Med Ind", "Incident/Report Only", "Litigated Text"}),
#"Reordered Columns" = Table.ReorderColumns(#"Kept Errors", {"Row Number", "Insured Name", "AsOf", "Policy Number", "Policy Inception Date", "Policy Expiration Date", "Insurer", "Claim Number", "Claimant First Name", "Claimant Middle Name", "Claimant Last Name", "Loss Date", "Claim Status", "Claim Type", "Risk State", "Jurisdiction", "Gender", "Claimant Birth Date", "Claimant Age", "Claimant Hire Date", "Years Employed", "Occupation Desc", "Average Weekly Wage", "Class Code", "Closed Date", "Reopened Date", "Litigated", "Date Reported To Insurer", "Days To Report", "Date Employer Informed", "Reporting Lag", "Claims Professional", "Reporting Location Desc", "Reporting Location Address1", "Reporting Location Address2", "Reporting Location City", "Reporting Location State", "Reporting Location Zip Code", "Injury Location Desc", "Injury Location Address1", "Injury Location Address2", "Injury Location City", "Injury Location State", "Injury Location Zip Code", "Reporting Group OrgID", "Reporting Group Code Path", "Reporting Group 1", "Reporting Group 1 Code", "Reporting Group 2", "Reporting Group 2 Code", "Employee ID", "Injury Cause Desc", "Nature Of Injury Desc", "Body Part Desc", "How Injury Occurred", "Injury Desc", "Activity", "Work Start Time", "Time of Incident", "PD Rating", "Medical Incurred", "Medical Paid", "TD Incurred", "TD Paid", "PD Incurred", "PD Paid", "VR Incurred", "VR Paid", "Expense Incurred", "Expense Paid", "Total Gross Incurred", "Total Outstanding Reserve", "Total Gross Paid", "Total Recoveries", "Third Party Recoveries", "Deductible Reimbursed", "Deductible Amount", "Deductible Type", "Day", "Year", "Month", "Month of Loss Numeric", "Employee Lag", "Lagtime Reporting", "Lagtime Banding", "LOE by Day", "Reporting Location", "Incident Location", "Claimant Birth Date Text", "Claimant Name Combined", "Age Range", "LOE By Year", "LOE Range", "LOE by Year Rounded", "LOE Label1", "LOE Label2", "LOE Label2 Sort", "Time Started", "Time Injured", "Time at Work", "Time to Injury Group", "Employer Lag Range", "Overall Lag Reporting", "Overall Lag Range ", "Shift Range", "Day Order", "Employee Lag Range Days", "Employer Lag Range Days", "Policy Inception Date Text", "Employee Lag Range Days Sort", "Employer Lag Range Days Sort", "Claimant Birth Date Text and Dash", "Merged Claimant Name and Birth Date", "Merged Injury Location No Zip Code", "Injury Location Merged", "Shift", "Shift Order", "24 Hour Round", "Claim Type All", "Claim Type First Aid Med Ind", "Incident/Report Only", "Litigated Text"})
in
#"Reordered Columns"
