Changing Data Type

[Theobu]

I am trying to change the data type in the columns that are displayed in text format. The source CSV file is comma delimited. The decimal separator is a PERIOD which is set in the EXCEL ADVANCED options "USE SYSTEM SEPARATORS" which is a PERIOD.

However, for some reason the decimal figures are seen as DATA TYPE: TEXT. When changing it to DECIMAL NUMBER in the TRANSFORMATION function, it comes back with an ERROR: "DataFormat.Error: We couldn't convert to Number.
Details:
2.18240547"

There are no non-numerical characters in these columns that contain decimal numbers but DATA TYPE remains TEXT.

The same source is used on another machine (Windows 11) and the data is displayed correctly.

I tried quite a couple of options to get the data type correct in order for Power BI to generate the required visual, with no success. 

I inserted custom column with the formula as below, and the result comes back with NULL

try Number.FromText([YourColumnName][Value]) otherwise null

 What do I miss here? 

[Theobu]

Thanks for the prompts response. It is highly appreciated. It is indeed tricky. All the suggestions came to "NULL" 🙂

[123abc]

It sounds like you’re dealing with a tricky issue in Power BI. Here are a few steps you can try to resolve the data type conversion problem:

1. Check Regional Settings: Ensure that the regional settings on both machines are identical. Sometimes, differences in regional settings can cause issues with number formats.

2. Clean the Data: Use Power Query to clean the data before converting it. You can remove any leading or trailing spaces and ensure there are no hidden characters. Here’s how you can do it:

   • Go to the Power Query Editor.
   • Select the column you want to clean.
   • Use the Trim and Clean functions to remove any unwanted characters.

3. Replace Errors: Before converting the data type, you can replace errors in the column to handle any unexpected values:

   • In the Power Query Editor, right-click the column header.
   • Select Replace Errors and provide a default value (e.g., 0).

4. Custom Column Formula: Your custom column formula looks good, but ensure that [YourColumnName] is correctly referenced. Here’s a slightly modified version:

   try Number.FromText([YourColumnName]) otherwise null

5. Locale Settings: When importing the CSV file, specify the locale settings to ensure that the decimal separator is correctly interpreted:

   • In the Power Query Editor, go to Home > Advanced Editor.
   • Add the following line to specify the locale:

     Source = Csv.Document(File.Contents("YourFilePath"), [Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None, Locale="en-US"])

6. Manual Conversion: If the above steps don’t work, you can manually convert the text to numbers using a custom function:

   (textValue as text) as number =>
   let
       cleanedText = Text.Trim(textValue),
       numberValue = try Number.FromText(cleanedText) otherwise null
   in
       numberValue

Try these steps and see if they help resolve the issue. If the problem persists, it might be useful to compare the Power BI and Excel settings on both machines to identify any discrepancies. Let me know if you need further assistance!