Changing Data Type

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!