Forum Discussion
Detected Type Mismatches
Hi,
I have been using Power Query to clean up my data and upon completion some Query Errors have appeared - they are just 30 Rows in total, the 'Detected Type Mismatches' code is:
= 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)
Can anyone help me with this?
Thanks
Hi ArchStanton ,
Thanks for reaching out to the Microsoft fabric community forum.Thanks for sharing your query. Based on your explanation and the screenshot, it seems that the "Detected Type Mismatches" step in your Power Query is trying to validate whether the values in each column match the expected data type like number, text, date, etc. The error you're getting usually comes when some values in the columns are not matching the expected type. For example, if a column is supposed to be a number, but there is some text or null value in it, Power Query throws that mismatch error.
Since you mentioned there are only 30 rows showing this error, it looks like just a few values are not matching the type. The step "Kept Errors" that you’ve already added will help you find out exactly which rows and columns are causing the issue. You can check those rows and see if the values are invalid or need to be cleaned up or converted before applying this step.
If you're only trying to clean the data and these mismatches are not critical, you can handle them by either fixing or converting the values before this step, or by applying column type changes using Table.TransformColumnTypes.
If you’re still stuck, feel free to share a few sample rows (just structure, no sensitive info), and we’ll help you fix it.
Thank you.
2 Replies
Hi ArchStanton ,
Thanks for reaching out to the Microsoft fabric community forum.Thanks for sharing your query. Based on your explanation and the screenshot, it seems that the "Detected Type Mismatches" step in your Power Query is trying to validate whether the values in each column match the expected data type like number, text, date, etc. The error you're getting usually comes when some values in the columns are not matching the expected type. For example, if a column is supposed to be a number, but there is some text or null value in it, Power Query throws that mismatch error.
Since you mentioned there are only 30 rows showing this error, it looks like just a few values are not matching the type. The step "Kept Errors" that you’ve already added will help you find out exactly which rows and columns are causing the issue. You can check those rows and see if the values are invalid or need to be cleaned up or converted before applying this step.
If you're only trying to clean the data and these mismatches are not critical, you can handle them by either fixing or converting the values before this step, or by applying column type changes using Table.TransformColumnTypes.
If you’re still stuck, feel free to share a few sample rows (just structure, no sensitive info), and we’ll help you fix it.
Thank you.
Thanks for the quick reply, I've managed to fix it using your advice, I was getting null values in what should have been a text column - the column was formatted incorrectly originally. Thanks for your help with this!!
