How to Format Data Types with Text and Numbers??

[jcarbone]

Hi all,

I need some help formatting a specific data type, the column I want to load into Power BI contains values like this:
A01.04

When I try loading this into Power BI it gives me a bunch of errors for the column.

If I format it as text it says the numbers are incorrect and if I format as a decimal number then the letters throw an error.

How can I transform this to read properly without errors?

I need to keep the decimals in as that is an important part of the identifier so any help would be appreciated.

Thanks!

[AbhinavJoshi]

Hi, @jcarbone. When you first load the data, Power BI would give you an option to detect data types based on first 200 or 1000 rows. Disable that or in the one of your steps it should say, "changed type". Remove the default change type for your column "DIAG_STD_CD" and see if it resolves the error. If the erros are resolved then format it to the desired data type. Also check that you get the data without erros from your soruce. Hope it helps.  

[Greg_Deckler]

@jcarbone How are you formatting it as in are you specifying the column type in Power Query Editor or once it's loaded into the model or ? If in Power Query Editor you don't specify a column type then it should accept both text and numbers. It's all treated as text essentially and your column header should have a ABC123 icon.

[jcarbone]

Hi Greg thanks for the response.

This is all within Power Query when I first connect and trasform the data.

Power BI reads this column as a decimal number and has changed type and here are the current results:

I don't see the option to change to a type that is "ABC123" either though.