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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors