Conditional formating column with "Locale" number settings

Hello Pasheim 

what you can do here is to check wheter your number-column contains any ".". If yes, your format it as US otherwise your transform your columns and deleting the space and then transform it to DE. If your column is containing both types at the same, then there is no way to proceed.

This code you can check for "." and fill the variable with "US" or "DE"

CheckType = if List.AnyTrue(List.Transform(Source[Column1], each Text.Contains(_,"."))) then "US" else "DE"

after that you make the transformation accordingly like this

    GetFormatedTable = if CheckType = "US" then 
        Table.TransformColumnTypes(Source, {{"Column1", type number}}, "en-US")
        else 
        Table.TransformColumnTypes(Table.TransformColumns(Source, {{"Column1", each Text.Replace(_," ", "")}}), {{"Column1", type number}}, "de-DE")

here the complete code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxVNJRMjZVitUBcYyAHEMdONcYxFUwMDIBC8UCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Column1 = _t]),
    CheckType = if List.AnyTrue(List.Transform(Source[Column1], each Text.Contains(_,"."))) then "US" else "DE",
    GetFormatedTable = if CheckType = "US" then 
        Table.TransformColumnTypes(Source, {{"Column1", type number}}, "en-US")
        else 
        Table.TransformColumnTypes(Table.TransformColumns(Source, {{"Column1", each Text.Replace(_," ", "")}}), {{"Column1", type number}}, "de-DE")
in
    GetFormatedTable

Copy paste this code to the advanced editor in a new blank query to see how the solution works. Just change the first step by clicking on the setting button and add or the US-variant or the other.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Hi Jimmy,
As a newbie, it took me a while to understand what the code was all about, but I found a solution in the end. Your suggestion helped a lot.
I had to check if any number had "." (dot) in them, but also if any numers has "," (comma) in them. If there were both some numbers with dot, as well as some numbers with comma (both = true), then I know i have to convert to numbers "en-US". If both are not true, I know I can convert the column to numbers as is, as any spaces as 1k separator will be detected and taken care of. (if I tried to convert to "en-DE", I got errors when space was used as 1k separator)

So for any newbies out there who could benefit from this, this is the code I used:

let
    Source = Csv.Document(File.Contents("C:\Test\Separator Example 1.csv"),[Delimiter=",", Columns=7, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    
    // Just promoting headers from the .csv
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

    // Spliting out disk size type from numbers (MB, GB, TB) to get numbers only
    #"Split Column by Delimiter" = Table.SplitColumn(#"Promoted Headers", "Virtual Disk:scsi0:0|Configured", Splitter.SplitTextByEachDelimiter({" "}, 
        QuoteStyle.Csv, true), {"Virtual Disk:scsi0:0|Configured.1", "Virtual Disk:scsi0:0|Configured.2"}),

    // Renamed the column with numbers to "Disk Size"
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Virtual Disk:scsi0:0|Configured.1", "Disk Size"}}),
   
    // Check if any number in column has dot as decimal separator and return value TRUE for CHECKFORDOT
    CHECKFORDOT = if List.AnyTrue(List.Transform(#"Renamed Columns"[Disk Size], each Text.Contains(_,"."))) then true else false,

    // Check if any numbers contain comma as separator and return True as value for CHECKFORCOMMA
    CHECKFORCOMMA = if List.AnyTrue(List.Transform(#"Renamed Columns"[Disk Size], each Text.Contains(_,","))) then true else false,

    // When both CHECKFORDOT and CHECKFORCOMMA are TRUE, convert columns to US number format. If one is FALSE, just convert the column format from text to numbers as is.
    CHANGETONUMBER = if CHECKFORDOT = true and CHECKFORCOMMA = true then Table.TransformColumnTypes(#"Renamed Columns",{{"Disk Size", type number}}, "en-US")
        else Table.TransformColumnTypes(#"Renamed Columns",{{"Disk Size", type number}})
in
    CHANGETONUMBER