cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate I
Advocate I

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.  Power Query auto detects this column as numeric and when I load the data into Excel or PowerBI it returns all the account numbers with letters in them as errors, which I understand.  However I need to have all account numbers shown, so I tried to select data type as text, but this doesn't work either and the error report says that Power Query is still trying to convert these alpha strings into numeric.

 

Any help would be much appreciated!

 

Matt

1 ACCEPTED SOLUTION
Community Champion
Community Champion

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.

 

Specializing in Power Query Formula Language (M)

View solution in original post

5 REPLIES 5
Community Champion
Community Champion

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.

 

Specializing in Power Query Formula Language (M)

View solution in original post

This did it thanks!

 

As you said, I went to the auto-generated steps where Power Query first decides on the data type of each field and changed the "Account" type to text.  Sorted.  Thankyou.

 

Super User I
Super User I

when you say you tried to change it to text its still keep it as numeric it what do you mean, it should correct the problem if you change it to text, can i see your power query steps?  





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

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).

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors