Forum Discussion

fcerullo's avatar
fcerullo
Icon for Advocate I rankAdvocate I
4 years ago
Solved

DataFormat.Error: We couldn't convert to Number but I am using Text columns

  I get this error in both PowerBI Desktop Query Editior, and in Excel Power Query, when I am merging two queries.   Each query only has a few columns and they are all formatted as Text. I have co...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    4 years ago

    fcerullo 

    Sometimes PowerQuery will try to help by adding a step of "Change Type" where it looks at the first 1000 rows and tries to determine if it is a number or text etc.  Did it add a step like that when you loaded your data from the source?  If so, you can just delete that step to stop it from trying to convert.

jdbuchanan71's avatar
jdbuchanan71
Icon for Super User rankSuper User
4 years ago

fcerullo 

Sometimes PowerQuery will try to help by adding a step of "Change Type" where it looks at the first 1000 rows and tries to determine if it is a number or text etc.  Did it add a step like that when you loaded your data from the source?  If so, you can just delete that step to stop it from trying to convert.

  • fcerullo's avatar
    fcerullo
    Icon for Advocate I rankAdvocate I
    4 years ago

    I added a step to make the format Text on all columns of these 2 queries.

     

    • fcerullo's avatar
      fcerullo
      Icon for Advocate I rankAdvocate I
      4 years ago

      oh, I see what your saying JD, I will take that auto-created step out and then do my own transformation.

      • fcerullo's avatar
        fcerullo
        Icon for Advocate I rankAdvocate I
        4 years ago

        This worked!

        The PQ was directing me to an error at a the Expand table step, but, it was actually caused earlier in my transformation.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    This helped me aswell...thanks

  • RTriangle's avatar
    RTriangle
    New Member
    2 years ago

    Thanks a lot!

  • amessing's avatar
    amessing
    New Member
    1 year ago

    I have this exact error and when the files are expanded it assigned a type of text, which seems correct.  Any other ideas of what could be going wrong?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 months ago

      I had the same issue. For me it was caused by the fact, that one of the key values i was merging two queries with wasn't a type number and there was a special character in it.
      So i just converted it to a number and replaced the Errors.