Data Format.Error Invalid Cell Value #REF!

[gilberttanjh]

Hi all,

I have a power query that aggregate the data from 21 excel files into 1 excel file. Recently during a data refresh, i kept getting the error - Dataformat.error Invalide cell value "#Ref!". I went back to all the 21 excel files but still cannot find the cell with that error value. Is there a better way that i can narrow down this error to resolve it? I read on the forum to duplicate the query and remove all steps except the source and navigation steps. Appreciate if someone can point me to what are the source and navigation steps per below picture.

Thanks.

[v-tsaipranay]

Hi @gilberttanjh ,

Thanks for reaching out to the Microsoft fabric community forum.

As per @danextian   posted answer those are the troubleshooting steps to resolve your issue.

In the image you referred to, it appears that only the source is shown, without any navigation steps. The rest of the content consists of applied steps. By examining the applied steps in the image, we can't identify the exact error that is occurring.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

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

Thank you.

[v-tsaipranay]

Hi @gilberttanjh 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please 'Accept it as a solution' and give a 'Kudos' so other members can easily find it.

Thank you.

[v-tsaipranay]

Hi @gilberttanjh,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-tsaipranay]

Hi @gilberttanjh,

I wanted to follow up on our previous suggestions regarding the error - Dataformat.error Invalide cell value "#Ref!". We would love to hear back from you to ensure we can assist you further.

If my response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

Thank you.

[danextian · Accepted solution]

Hi @gilberttanjh 

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.