Forum Discussion

mattymc1984's avatar
mattymc1984
Advocate I
9 years ago
Solved

columns with mixed data types

Hi,   I'm working with debtor/invoice data.  One of my fields is "Account Number".  Roughly 80% of these are numeric, however internal customer numbers have a letter prefixing the account number.  ...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    If you add an extra step to change number back to text, the errors won't be corrected, so you should adjust the autogenerated step in which the data types were changed and adjust "number" to "text" for the Account Number field.

     

mattymc1984's avatar
mattymc1984
Advocate I
9 years ago

This is the line of code for changing the data type:

 

 #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Account", type text}}),

 

However, when I load the data into Excel, it still returns all the alpha account numbers as errors:

 

DataFormat.Error: We couldn't convert to Number.
Details:
    A003

MarcelBeug's avatar
MarcelBeug
Community Champion
9 years ago

Refereing to my previous post: that doesn't look like the line of code with the initial type change (because the "1" in the step name and because it is referencing previous step #"Filtered Rows") .

 

My suggestion would be to delete this step and adjust the #"Changed Type" step (probably the second or third step in your code).