Forum Discussion

yc-bsa's avatar
yc-bsa
Regular Visitor
4 years ago

Data Format Error: contain dash "-"

Hello all, 

I am trying to load my data into Excel but have error with one of the columns (please see below). 

I tried to put the data type as 'any' but still shows error. I tried using replace, but it doesn't work if the data type is 'any' (i.e. it shows an error message when I type "-" in find value).

If I change data type to 'text', the function will go through (i.e. system let me put in find "-") but nothing would happen (i.e. doesn't replace anything). 

Would anyone know what I should do here? 

Thanks heaps. 

 

Need Help

11 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    Hi yc-bsa ,

     

    I'm not entirely clear what you're asking here. Are you trying to do a text replacement in Power Query but it's not working?

    In terms of the data types, it looks like the column you are referencing should be TEXT type. You shouldn't export anything out of Power Query as ANY type.

    Once you've changed the data type to text, try and do whatever it is you are trying to do and update here with clear details as to what you have tried (M code included please) and what error you get, or why the results aren't as you expect.

     

    Pete

    • yc-bsa's avatar
      yc-bsa
      Regular Visitor
      4 years ago

      Hi BA_Pete ,

      Thanks for your reply and suggestion. 

      I changed the column to type TEXT and tried exporting but it came back with the same error message. 

      Column 'Project' where I am having problem with - the cells where it comes up as error, the content is in the format 'AAA-111' (without ' '). 

      This same error message comes up when I change column to type TEXT or type ANY. 

      I just want to export the file as they are. 

       

      • BA_Pete's avatar
        BA_Pete
        Super User
        4 years ago

        Hi yc-bsa ,

         

        Can you provide your M query please?

        Select the query that you're having trouble with, open Advanced Editor and copy out everything in there and paste into a code window ( </> button) here. Remove sensitive items from the source step, such as file/server paths.

         

        My guesses so far:

        1) there may be a function later in your M code that is trying to convert this field back to a number.

        2) the Excel destination may be preformatted as number type, so the error is throwing there rather than in Power Query.

         

        Pete