Query Errors

[ArchStanton]

I have recently updated the excel workbook that is the source file for a PowerBI report with an X-Lookup to bring in values into a column. When I refreshed PowerBI I noticed that a 'Query Errors' folder was automatically generated - it says Detected Type Mismatches in the Applied Steps window: 

 

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

The 'Quality Slide' which is the name of the excel worksheet looks ok when I view it - can someone explain what may have happened and whether this query error folder can be ignored / deleted? 

From what I can see it hasn't prevented me doing what I need to do but I'd prefer not have these errors showing up in Power query!

Thanks

[v-pnaroju-msft · Accepted solution]

Thankyou, @m4ni, for your response.

Hi ArchStanton,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

Based on my understanding, the "Query Errors" folder appears due to data type mismatches caused by the XLOOKUP formula in your Excel source. During a Power BI refresh, if values such as #N/A or unexpected text are detected that do not conform to the expected data type of the column, the "Detected Type Mismatches" step is triggered. Power BI automatically generates an error-tracking query when column values—especially those derived from Excel formulas like XLOOKUP—do not match the defined data types, for example, text appearing in a numeric column.

Please follow the steps outlined below, which may help resolve the issue:

1. Modify your Excel formula by wrapping the XLOOKUP with IFERROR(), for example:
   =IFERROR(XLOOKUP(...), "")
2. Handle errors within Power Query by replacing or suppressing mismatches as shown:
   Table.TransformColumns(Source, {{"ColumnName", each try _ otherwise "Missing"}})
3. Explicitly apply the correct column data types, for instance:
   Table.TransformColumnTypes(Source, {{"ColumnName", type text}, {"Col2", type number}})
4. Delete the "Errors in Quality Slide" query and refresh the data. If the issue is resolved properly, this query will not reappear.

Additionally, for your reference, please find the following helpful links:
Error handling - Power Query | Microsoft Learn
Tutorial: Shape and combine data in Power BI Desktop - Power BI | Microsoft Learn
Best practices when working with Power Query - Power Query | Microsoft Learn

If our response is helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.

Should you have any further questions, please feel free to contact the Microsoft Fabric community.

Thank you.

[ArchStanton]

Thank you for the very comprehensive and detailed reply - its much appreciated. I wrapped the X-Lookup in an IFERROR statement and then deleted the errors table, after refreshing the report the Errors folder did not reappear! 
Many thanks..

[m4ni]

Hi @ArchStanton 

you can certainly try deleting the errors folder and refreshing your data agaian.

If however the errors folder is recreated each time then that signals a problem somewhere.

In some cases though the folder creates for no apparent reason so no harm in deleting and refreshing the file first to asses.