Conditional formating column with "Locale" number settings

[Pasheim]

Hi,
I have a column with decimal numbers (Size.1). However, sometimes the source file uses period as decimal separator and comma as thousend separator, and sometimes the source file uses comma as the decimal separatore and space as thousend separator.

I am looking for a solution that -> if I find a period in "Size.1" I want to format the column with "Lokale - Numbers - English US", and then continue the query. If I don't find any periods in "Size.1", the format is already correct, and I want to just continue with the query.

One of my approaches has been:
#"ADD COLUMN SORT" = Table.AddColumn(#"SPLIT BY SIZE TYPE", "ColumnSort", each if Text.Contains([Size.1], ".") then 1 else 2),

(for identifying which rows has period in the number)

#"Sorted Rows" = Table.Sort(#"ADD COLUMN SORT",{{"ColumnSort", Order.Ascending}}),

(for making sure that the lines with period are at the top of the table)

#"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "NumberFormat", each if [ColumnSort] = 1 then Table.TransformColumnTypes(#"SPLIT BY SIZE TYPE", {{"Size.1", type number}}, "en-US") else [Size.1]),

(this adds a table (NumberFormat), where the cells corresponding with lines in "Size.1"  with periods are filled with a Table. The rest is filled with data from  "Size.1")

NumberFormat = #"Added Conditional Column"{0}[NumberFormat],

(This step is the result of clikking on the top row table for expanding it. It does the job, and converts all numbers in "Size.1" to the correct format. However, if the top row does not contain a table (the format is already OK), this step generate an error and stops the whole rest of the query.)

Hope someone have a suggestion for a solution

PAs

[Jimmy801 · Accepted solution]

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

[Jimmy801 · Accepted solution]

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

[Pasheim]

Hi Jimmy,
Thank you for responding, and please excuse me for not quite understanding your suggestion. I am fairly new to Power Query. But if you can relate this to my example here, that would be very helpful. I am learning by the minute 🙂
I just import the .csv file, and remove the text MB, GB, TB from the numbers. Then I want to change the 1K and decimal separators. So yes, I can look for the period, which probably can tell tell me which format the numbers are in (US).  This will of course be wrong, if there are no decimal numbers in the colunm. In that case, the format is set to DE and the 1k separator (comma) will be interpreted as a decimal separator, converting 1000 (1,000) to 1,00. But for now, lets say that there always will be a decimal number in the column at any given time. So , we should check if the column has period in a number, and then setchange the column format type to DE-numbers. If not - we just change the column type to numbers as it is.
So how does you example fit into this? Example attached.

[Jimmy801]

Hello @Pasheim 

if you have a mixture of number types in one column there is no chance to find a logic. I already wrote you how my approach is working, but also difficult to implement for a newbie. 

You have to copy paste my last 2 steps into andavanced editor at the end.. before the "in"-statement and connect them. You have also to adapt the column names form Column1 to #"Size.1"

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

[Pasheim · Accepted solution]

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

[smpa01]

@Pasheimcan you give a sample data?

[Pasheim]

Sample 1 = (comma as 1k separator and period as decimal separator - as text column)

Sample 2 = (space as 1k separator and comma as decimal separator - as text column)

Thank you for responding.
These files comes as .csv files. Sometimes in the format of "Sample 1" and other times as "Sample 2". I am trying to get the query to find out which format is used, and convert it automatically. I want the end result to end up as the format in "Sample 2", with space (or nothing)  as 1k separator and comma as decimal separator. My Excel template (with power query) collect the correct file automatically, but cannot idetify the format of the numbers (so far)