Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
nbtong
Frequent Visitor

Detected Type Mismatches

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. 

 

 

nbtong_0-1716321312537.png

 

nbtong_1-1716321346680.png

 

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"

 

 

 

1 ACCEPTED SOLUTION
jennratten
Super User
Super User

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.

View solution in original post

5 REPLIES 5
jennratten
Super User
Super User

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
Super User
Super User

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
Super User
Super User

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.

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.! 

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors