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, bu...
Need Help
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

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

      Hi BA_Pete ,

       

      Thanks for your reply. 

      Do you need the full M Code from the beginning? Or the M code for the column that I am having trouble with? For the later, please see below:

      <#"Changed Type2" = Table.TransformColumnTypes(#"Filtered Rows4",{{"Project", type text}})>
      This is the last line of the codes and I am trying to export the query after this. 

       

      Cheers

      • BA_Pete's avatar
        BA_Pete
        Super User
        4 years ago

        Hi yc-bsa ,

         

        Can you copy/paste your whole M code query from Advanced Editor here please? I think you are trying to convert this field to a number earlier in your query and the error is carrying forward to the end of the query.

         

        When you paste the code here, hit this button and paste in there to keep everything nice and tidy/readable:

         

        Pete