Error: Detected type mismatches

[GraceTCL]

hi can anyone advise on this error "Detected type mismatches" during query where error details as follow. This is despite the fact that I have reverted the change type of the source data back to its original form.

"= 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)"

These are my steps after I loaded using SQL server.

[cjohnson]

I would consolidate your Changed Type steps to 1 or as few as possible and place them as early in the flow as you can.

[Anonymous]

I am having same issue. However I am connecting to Sharepoint folder to read in all files and file names. No changes to my data type is detected. However,  I did create a copy of substring column and formatted it as date. 

[cjohnson]

I ran into a similar issue that was giving me a type mismatch in similar circumstances.

Check if any of those "Changed Type" steps in your PowerQuery flow assign a data type you don't intend. Mine was fixed because it was assigning a text field as int.64 by default, this was creating a type mismatch when I then tried to convert to text.

Not sure exactly why it fixed it for me, but your issue seems similar to the issue I had.

[Danish_Bhat]

is there any further help for the above post as am also getting the same error while the data is being pulled from share point source.

[jehsu]

Hello - I am wondering if there was a resolution to this, as I have inherited a Power Bi report from someone no longer at the organization and getting the same Detected Type Mismatches error, and I am not able to tell from the error where is the issue.

[v-henryk-mstf]

Hi @GraceTCL ,

This issue appears when your data structure changes and data type conversion failed.(due to data type not matched) Can you confirm these files has same data structure?(e.g. data type, field name, separator... )

You can try In Query Editor, right-click the column and use Replace Values.

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GraceTCL]

Hi @v-henryk-mstf Thanks for your responses. The detailed error message was already in my initial message (unless I am still missing something).

How can I identify which are the columns with data type issue?

May I clarify this statement: "This issue appears when your data structure changes"

Do you mean the data source has structural changes or my additional steps to change the data type post pulling from source results in this error?

I had changed data type of "date_id" from whole number to date type but later removed this step. The error still persist thereafter. I am not sure if this step, which was the only step where I change the data type directly without duplicating the column first, resulted in the error.