Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Solved! Go to 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).
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.
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
will try this and update here
@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.
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"
@Anonymous
It's probably easier to delete the step from the Power Query UI:
Otherwise you'll be chasing down comma issues.
Do not see that step in my main query
@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).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |