Data Format.Error Invalid Cell Value #REF!

Hi Anonymous 

When you refresh in desktop and a query returns an error that it can handle, you  would normally get the kind of screen below. Clicking on the view errors will create a query showing all rows with errors only and which columns.

If you weren't prompted, try this query:

let
 //name of the query with errors, if there are characters under than an underscore the format should be #"Sheet 1" for a query named Sheet 1
Source = Sheet1,
  #"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", {"ColumnA", "ColumnB", "ColumnC", "ColumnD"}),
  #"Reordered Columns" = Table.ReorderColumns(#"Kept Errors", {"Row Number", "ColumnA", "ColumnB", "ColumnC", "ColumnD"})
in
  #"Reordered Columns"

You can right click the header of the column with errors and replace the values enrror with null, remove the erring rows or fix the data source.