Data rows error

[Anonymous]

Hi All i am creating a report using the .txt files from Azure Data Lake storage Gen 2. When i load the data i get an error message showing some rows were loaded and there were some errors on some rows. When i right clicked the "errors in  query1" advanced editor  i get the following error message. Can soneome please advice.

let
Source = Query1,
#"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 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", {"OBJECTID", "CASENUM", "CASEREPORTNUMBER", "SUPPLEMENT", "MASTREL", "CADAGENCY", "CMINVESTUNIT", "AGENCY", "ORGAN", "INCIDENTJURISDICTION", "SUBMITTALORI", "RAWLOCATION", "GPSUSED", "CSZ", "GEOGRAPHICAREA", "CADINCIDENTTYPE", "NIBRSPRIMSUBCAT", "CRCODE", "CRDESCRIPTION", "NIBRSCLASSHIERARCHY", "WEAPONIBRCODE", "PSCIPSUSPECTEDHATECRIME", "PSCIPINTERPRETERRESOURCEUSED", "PSCIPLANGUAGEUSED", "REPORTEDBY", "ASSISTEDBY", "MEDIATEXT", "DISPOSITION", "PSCUNFOUNDEDREASON", "DUPLICATEOFCASENUM", "CLEARANCEBASIS", "CLEAREDEXCEPTIONALLYON", "ENTEREDBY", "STLNRECFIREARM", "QCLEOKA", "UNFINCOTHERJURISDICTION", "UNFINCOJORI", "UNFINCOJCADAGENCY", "UNFINCOJOTHRDEPT", "PREMISETYPE", "FORCEUSED", "DOMESTICVIOLENCE", "FIREARMINVL", "HATECRIME", "GANG", "DRUGPARAINVL", "SEXCRIME", "IMMDETMENWRT", "NFGW", "WARRANTCOUNT", "FELCHARGECOUNT", "MISDCHARGECOUNT", "CITINFCHARGECOUNT", "OUTRIGHTARRESTCOUNT", "SUMMONSARRESTCOUNT", "WARRANTARRESTCOUNT", "NARCANCOUNT", "NSTOLENPROPTOTAL", "NRECPROPTOTAL", "PUBLICFLAG", "MOTOCRINSERTEDDATE", "P1RMSUPDATEDDATE"}),
#"Reordered Columns" = Table.ReorderColumns(#"Kept Errors", {"Row Number", "OBJECTID", "CASENUM", "CASEREPORTNUMBER", "SUPPLEMENT", "MASTREL", "CADAGENCY", "CMINVESTUNIT", "AGENCY", "ORGAN", "INCIDENTJURISDICTION", "SUBMITTALORI", "RAWLOCATION", "GPSUSED", "CSZ", "GEOGRAPHICAREA", "CADINCIDENTTYPE", "NIBRSPRIMSUBCAT", "CRCODE", "CRDESCRIPTION", "NIBRSCLASSHIERARCHY", "WEAPONIBRCODE", "PSCIPSUSPECTEDHATECRIME", "PSCIPINTERPRETERRESOURCEUSED", "PSCIPLANGUAGEUSED", "REPORTEDBY", "ASSISTEDBY", "MEDIATEXT", "DISPOSITION", "PSCUNFOUNDEDREASON", "DUPLICATEOFCASENUM", "CLEARANCEBASIS", "CLEAREDEXCEPTIONALLYON", "ENTEREDBY", "STLNRECFIREARM", "QCLEOKA", "UNFINCOTHERJURISDICTION", "UNFINCOJORI", "UNFINCOJCADAGENCY", "UNFINCOJOTHRDEPT", "PREMISETYPE", "FORCEUSED", "DOMESTICVIOLENCE", "FIREARMINVL", "HATECRIME", "GANG", "DRUGPARAINVL", "SEXCRIME", "IMMDETMENWRT", "NFGW", "WARRANTCOUNT", "FELCHARGECOUNT", "MISDCHARGECOUNT", "CITINFCHARGECOUNT", "OUTRIGHTARRESTCOUNT", "SUMMONSARRESTCOUNT", "WARRANTARRESTCOUNT", "NARCANCOUNT", "NSTOLENPROPTOTAL", "NRECPROPTOTAL", "PUBLICFLAG", "MOTOCRINSERTEDDATE", "P1RMSUPDATEDDATE"})
in
#"Reordered Columns"

[pranit828]

HI @Anonymous 

You might want to take one step at a time.

Step 1 : Add the source. Doet it error out? No, then no issue importing the file.

Step 2: Change Data type one column at a time. Eventually you will need to assign type to most of these columns. You will know which column has an issue.

[Anonymous]

will try this and update here

[swise001]

@Anonymous 

Step back in your query transformations and make sure that Power Query isn't 'automatically' assigning the datatype to the columns.  This could lead to these errors; 

"Message = "The type of the value does not match the type of the column.", Detail = v], fieldTypes{i}})"

Instead - remove the "Changed Type" step or explicitly call out the column data types yourself as part of the query.   This may be part of the problem. 

[Anonymous]

Thanks Swise01

So in the main query i have , so delete the entire part from (#"Changed type " till the end?

 #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"OBJECTID", Int64.Type}, {"CASENUM", type text}, {"CASEREPORTNUMBER", type text}, {"SUPPLEMENT", Int64.Type}, {"MASTREL", type text}, {"CADAGENCY", type text}, {"CMINVESTUNIT", type text}, {"AGENCY", type text}, {"ORGAN", type text}, {"INCIDENTJURISDICTION", type text}, {"SUBMITTALORI", type text}, {"RAWLOCATION", type text}, {"GPSUSED", type text}, {"CSZ", type text}, {"GEOGRAPHICAREA", type text}, {"CADINCIDENTTYPE", type text}, {"NIBRSPRIMSUBCAT", type text}, {"CRCODE", type text}, {"CRDESCRIPTION", type text}, {"NIBRSCLASSHIERARCHY", type text}, {"WEAPONIBRCODE", Int64.Type}, {"PSCIPSUSPECTEDHATECRIME", type text}, {"PSCIPINTERPRETERRESOURCEUSED", type text}, {"PSCIPLANGUAGEUSED", type text}, {"REPORTEDBY", type text}, {"ASSISTEDBY", type text}, {"MEDIATEXT", type text}, {"DISPOSITION", type text}, {"PSCUNFOUNDEDREASON", type text}, {"DUPLICATEOFCASENUM", type text}, {"CLEARANCEBASIS", type text}, {"CLEAREDEXCEPTIONALLYON", type datetime}, {"ENTEREDBY", type text}, {"STLNRECFIREARM", type text}, {"QCLEOKA", type text}, {"UNFINCOTHERJURISDICTION", type text}, {"UNFINCOJORI", type text}, {"UNFINCOJCADAGENCY", type text}, {"UNFINCOJOTHRDEPT", type text}, {"PREMISETYPE", type text}, {"FORCEUSED", type text}, {"DOMESTICVIOLENCE", type text}, {"FIREARMINVL", type text}, {"HATECRIME", type text}, {"GANG", type text}, {"DRUGPARAINVL", type text}, {"SEXCRIME", type text}, {"IMMDETMENWRT", Int64.Type}, {"NFGW", type text}, {"WARRANTCOUNT", Int64.Type}, {"FELCHARGECOUNT", Int64.Type}, {"MISDCHARGECOUNT", Int64.Type}, {"CITINFCHARGECOUNT", Int64.Type}, {"OUTRIGHTARRESTCOUNT", Int64.Type}, {"SUMMONSARRESTCOUNT", Int64.Type}, {"WARRANTARRESTCOUNT", Int64.Type}, {"NARCANCOUNT", type text}, {"NSTOLENPROPTOTAL", Int64.Type}, {"NRECPROPTOTAL", Int64.Type}, {"PUBLICFLAG", type text}, {"MOTOCRINSERTEDDATE", type datetime}, {"P1RMSUPDATEDDATE", type datetime}})
in
    #"Changed Type"

[swise001]

@Anonymous 

It's probably easier to delete the step from the Power Query UI: 

Otherwise you'll be chasing down comma issues. 

[Anonymous]

Do not see that step in my main query

[swise001 · Accepted solution]

@Anonymous 

Delete the last step "Changed Type" .  

This should remove the error.  

You will also need to figure out the root cause of the error (i.e perhaps there are letters is a column that was mostly numbers - and Power Query was 'automatically' identifying that column as Whole Numbers).