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
GraceTCL
Helper II
Helper II

Error: Detected type mismatches

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.

Steps.PNG

7 REPLIES 7
cjohnson
Frequent Visitor

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.

ricky123
Regular Visitor

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
Frequent Visitor

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
Frequent Visitor

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
New Member

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
Community Support
Community Support

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

 

v-henryk-mstf_0-1619769106957.png

 

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.

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.

 

Capture.PNG

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.