Forum Discussion

JOLBR's avatar
JOLBR
New Member
5 years ago

Identical Excel formula = Different results in PowerQuery

Hi, 

 

I am a bit confused about this. 

 

I have an identical formula in 2 different, but comparable Excel Workbooks. The formula is as follows TEXTJOIN("-";TRUE;C12:L12)

 

Howe,ver when loading the documents to PowerQuery, one behaves as expected, but the second yields an Error, which I cannot find the reasoning for.

Excel formula resultPower Query result
Hyundai Power Transformers USA-United States-Alabama-Montgomery-54 WeeksHyundai Power Transformers USA-United States-Alabama-Montgomery-54 Weeks
Delta Star East -U.S-Virginia -Lynchburg-58-62 weeksDataFormat.Error: Invalid cell value '#NAME?'.

 

- The formula does not in Excel yeild a #N/A result

- Cells containing the formula in both documents are formatted as 'General' 

 

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    You say the Excel workbooks are different, but comparable.  How comparable?  Both .xlsx?  Both formulas in tables?  Any named ranges in one and not the other?  Any missing values that are causing the #NAME error?

    • JOLBR's avatar
      JOLBR
      New Member
      5 years ago

      Hi, 

       

      Thank you for your interest and response. 

      • Comparabale as completely identical in terms of structure, and formatting, but values of cells in documents may differ
      • Both files are .xlsx
      • Source data is not structured in tables but simply Excel cells
      • No named ranges in either document referring to the affected cells
      • Identical formula across documents resultign in a textstring (TEXTJOIN-formula) in Excel
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    JOLBR 

    Is there any blank cells between C12 to L12 in row 2, try change the data type to only text in power query, blank(text), null, and 0 can miss identified and could bring errors in PQ. 

     

    Paul Zheng _ Community Support Team

    • JOLBR's avatar
      JOLBR
      New Member
      5 years ago

      Hi Paul, 

       

      Thanks for the suggestions. 

       

      There are blank cells in the range, but there are blank cells for both files. That is why I do not understand why one file works while the other fails in loading that particular cell value. 

       

      Tried changing the resulting cell to "text" but that did not help. 

       

      However what did help was to, overwrtie the function --> save the file --> open the file again --> copy  the function from below cell (identical) and save the file agian. Then the cell was loaded correctly in PQ