Forum Discussion

JoshEnglish's avatar
JoshEnglish
Helper I
8 years ago
Solved

Getting Invalid Cell Reference after LeftAnti merge

I recently upgraded to Power BI Desktop Version: 2.59.5135.421 64-bit (June, 2018)

 

I have a large table (30K lines) with a few lines I needed to correct, so I copied the offending lines to a new Excel file, did a left Anti merge to remove them from my large table, then merged the corrections back into the data table.

 

Last month this worked like charm.

 

Now I get "DataFormat.Error: Invalid cell value '#REF!'." when doing the merge with JoinKind.LeftAnti.

 

Neither of the original Excel tables have any formulas or any cell with a #REF! error. 

 

If I do a RightAnti I get a table which I can expand, but I get an empty table when I expand the data.

 

To clarify: This is all in Power Query

 

If I do an InnerJoin I get the rows in my Corrections file and I can expand things just fine.

 

If I do a RightOuter join I get the same behavior as InnerJoin.

 

Any suggestions on getting LeftAnti joins to work in the June 2018 version?

  • I was barking up the wrong tree here. Someone entered #REF! as a value in a single cell in one of my sources. It wasn't a formula so Excel's find function couldn't find it.

     

    I fixed it with a ReplaceErrorValue function.

1 Reply

  • I was barking up the wrong tree here. Someone entered #REF! as a value in a single cell in one of my sources. It wasn't a formula so Excel's find function couldn't find it.

     

    I fixed it with a ReplaceErrorValue function.