Formatting Issue with Decimal Values Displayed as Text (Material Numbers)

[bbui11]

Hi,

I am importing a list of material numbers from an Excel file, but am running into some issues with the display of some decimal values when converting to text.

Excel File

- Stores the material numbers in a "General" format

- No formulas are used to populate the material number column

- The Excel file is generated as an export from an application, but unfortunately not exactly sure of the specific details.

Screenshot below shows the value in the formula bar matches with the formatted value within the cell. My understanding would be that this implys that the formatted value of the cell matches with the true value. Essentially not rounded or reformatted.

When the the Excel file is imported into PBI, the data type is defaulted to "Any" and the format of the values remain unchanged.

Screenshot below shows initial unchanged import format. Other examples are listed as well. 

Here is where it gets funky.

When I convert the data type of the Material Number column to "Text", the value adds additional characters. I duplicated the Material Number column to highlight the differences between the "Any" format vs. "Text" format.

Screenshot below shows the format changes after converting data type to "Text".

For some reason, only some of the values are affected. (Ex. 0.02035 does not change)

Any thoughts on what is causing this, as well as any recommedations on a workaround? 

Additional Information

- There are ~70,000 material number records with a wide range of formats (ex. letters only, dashes, etc.)

- I tried changing the data type within the Excel file from "General" --> "Text", which did not change the format of the values on the Excel file itself, but upon import the format of the value changes.

- In Power Query, tried to convert catalog number to Text --> Performed UPPER and TRIM transformation steps --> changed back to General, but the issue still persists

- I saved the Excel file as a CSV, and this resolved the issue, but is not a sustainable solution because the Excel file is a working document in which other users are making changes on multiple tabs.

- I can't leave the format as general, because I need to perform TRIM and UPPER transformation steps due to dirty data, in order to create relationships across different tables. 

[ronrsnfld · Accepted solution]

Seems like there are two issues.

1. A problem with your data:

 - The numbers that are right justified are real numbers, and are using the dot as the decimal

 - The number that is left justified is using the dot as the thousands separator

  You'll have to make your data consistent first.

2.  When you format the column as Text in Power Query, it is showing you the entire value that is stored as a number in Excel, hence the extra zero's and terminal value.

You first step will be to clean up the non-numeric data in the Material Number column. It is probably being imported incorrectly from the external application.