Detected Type Mismatches

[nbtong]

Hi experts,

As you see in the Applied Steps, I didn't change any data type, but I got the error message in the subject line.  When I got the message, the query group (Query Errors) was created.  How can I fix this?  Greatly appreciate it. 

Below is the query in the Advanced Editor.  There is no step for changing any data type. 

let
Source = T_ACADEMIC_HISTORY,
#"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", {I removed columns here to simply, "TITLE", "DM_DESC3", "DM_DESC6"}),
#"Reordered Columns" = Table.ReorderColumns(#"Kept Errors", {"Row Number", Also removed columns here to simply, "TITLE", "DM_DESC3", "DM_DESC6"})
in
#"Reordered Columns"

[jennratten]

Hi @nbtong - Have you had a chance to take a look at these?  Please consider clicking Accept as Solution if this answered your question.  Thanks!

[jennratten]

Hi @nbtong - just checking to see if you were able to resolve this.  Thanks!

If my response helped you please consider accepting it as the solution and/or liking it.

[jennratten · Accepted solution]

Hello - this table will automatically be created when you attempt to load data to the model and there are errors somewhere in the records.  The problem may or may not be a data type issue. The table created automatically includes the type mismatch step.  The best way to see the errors would be to view the errors table that was created.  Scroll through the columns and you should see a column which contains a value that looks like a hyperlink with the word Error.  If you click the hyperlink in the cell it will drill down to the error and tell you what the exact problem is.  You can also see the actual problem without drilling down by clicking on the white space next to the hyperlink (in the same cell) and a preview will appear which displays the actual error message.  I find that type errors are frequently numbers/dates that are attempting to be parsed as text or vise versa.

[nbtong]

Great!  Thank you for your kind explanation!  I have 3 million cases, so I'm not sure if I can locate the error cells, but will try and let you know.! 

[jennratten]

You shouldn't need to check each one individually.  Getting the error message for one cell and fixing it will usually resolve all the errors in the column.