Forum Discussion

Anonymous

Not applicable

5 years ago

Solved

Full-Width Characters? Power Query return value if contains certain text

Hi there,

While transforming data, I've created a conditional column, if any of certain text is contained in [Query], return "Branded" or "non-Branded" in [PQ Branded/Non-Branded].

Code

#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "PQ Branded/Non-Branded", each if Text.Contains(Text.Lower([Query]), "tesla") then "Branded" else if Text.Contains(Text.Lower([Query]), "auto-drive") then "Branded" else if Text.Contains(Text.Lower([Query]), "model") then "Branded" else if Text.Contains(Text.Lower([Query]), "tsla") then "Branded" else "non-Branded")

But after transforming, it's found some times even a value in [Query] contains the setting keyword, the affected cell in column [PQ Branded/Non-Branded] is still returning "non-Branded".

Then I found it seems because those characters are asian full-width characters.

In column [Branded/Non-Branded], it is using DAX to identify, which is successfully categorized to "Branded", but in column [PQ Branded/Non-Branded] failed.

Is there a way to make it succeed in Power Query as well? - coz there are over 300 million rows, using DAX leads to a lower performance issue.

Thanks & have a good day.

Need Help

Anonymous

5 years ago

HI Anonymous,

I think you can use character functions to do compare and with each character and convert them to a common text string before using the search function. (character function is based on ascii code)

Full query:

let
    Source =
        Table.FromRows(
            Json.Document(
                Binary.Decompress(
                    Binary.FromText(
                        "i45WMlTSUSpJLc5JVIrViVYygvGMwFxjIPf93inv97a+3zv5/d6e93sbweImQPEQ12AfR6XYWAA=",
                        BinaryEncoding.Base64
                    ),
                    Compression.Deflate
                )
            ),
            let
                _t = ((type nullable text)
                meta
                [
                    Serialized.Text = true
                ])
            in
                type table [
                    Index = _t,
                    Test = _t
                ]
        ),
    #"Changed Type" =
        Table.TransformColumnTypes(
            Source,
            {
                {
                    "Index",
                    Int64.Type
                },
                {
                    "Test",
                    type text
                }
            }
        ),
    #"Added Custom" =
        Table.AddColumn(
            #"Changed Type",
            "Check",
            each
                Text.Contains(
                    Text.Combine(
                        List.Transform(
                            Text.ToList(Text.Lower([Test])),
                            each
                                if
                                    Character.ToNumber(_)
                                    >= 65345
                                    and Character.ToNumber(_)
                                    <= 65370
                                then
                                    Character.FromNumber(Character.ToNumber(_) - 65248)//offset between half and full width characters
                                else
                                    _
                        )
                    ),
                    "tesla"
                )
        )
in
    #"Added Custom"

Range of character list:

half-width characters(upper/lower): 65 ~ 90 / 97 ~ 122
full-width characters(upper/lower): 65313 ~ 65338 / 65345 ~ 65370

Sample character list:

let
    Source =
        List.Combine(
            {
                List.Numbers(
                    Character.ToNumber("A"),
                    26
                ),//half-upper
                List.Numbers(
                    Character.ToNumber("a"),
                    26
                ),//half-lower                
                List.Numbers(
                    Character.ToNumber("Ａ"),
                    26
                ),//full-upper
                List.Numbers(
                    Character.ToNumber("ａ"),
                    26
                )//full-lower
            }
        ),
    #"Converted to Table" =
        Table.FromList(
            Source,
            Splitter.SplitByNothing(),
            null,
            null,
            ExtraValues.Error
        ),
    #"Added Custom" =
        Table.AddColumn(
            #"Converted to Table",
            "Custom",
            each Character.FromNumber([Column1])
        ),
    #"Renamed Columns" =
        Table.RenameColumns(
            #"Added Custom",
            {
                {
                    "Column1",
                    "character number"
                },
                {
                    "Custom",
                    "character"
                }
            }
        )
in
    #"Renamed Columns"

BTW, if you work with huge amount of table records, you can also do transform on search text instead of raw field value:

#"Added Custom" =
        Table.AddColumn(
            #"Changed Type",
            "Check",
            each
                Text.Contains(
                    Text.Lower([Test]),
                    "tesla"
                )
                or Text.Contains(
                    Text.Lower([Test]),
                    Text.Combine(
                        List.Transform(
                            Text.ToList("tesla"),
                            each Character.FromNumber(Character.ToNumber(_) + 65248)
                        )
                    )
                )
        )

Regards,

Xiaoxin Sheng

5 Replies

PC2790

Community Champion

5 years ago

Hello Anonymous ,

In the screenshot shown, the value in the query says- "tesla", however in your Power Query, it is not contained in any of your condition.

Hence it is not getting fulfilled and going in the "non branded" else statement.

can you recheck that?

Anonymous

Not applicable

5 years ago

Hi PC2790 , Thanks for pointing out. I've updated the sample to "each if Text.Contains(Text.Lower([Query]), "tesla")". - which is actually using in real data.

The point is, there are 4 values are not successfully categorized as they are ｔｅｓｌａ (the fullwidth characters).

Thanks for reply.

Anonymous

Not applicable

5 years ago

HI Anonymous,

I think you can use character functions to do compare and with each character and convert them to a common text string before using the search function. (character function is based on ascii code)

Full query:

let
    Source =
        Table.FromRows(
            Json.Document(
                Binary.Decompress(
                    Binary.FromText(
                        "i45WMlTSUSpJLc5JVIrViVYygvGMwFxjIPf93inv97a+3zv5/d6e93sbweImQPEQ12AfR6XYWAA=",
                        BinaryEncoding.Base64
                    ),
                    Compression.Deflate
                )
            ),
            let
                _t = ((type nullable text)
                meta
                [
                    Serialized.Text = true
                ])
            in
                type table [
                    Index = _t,
                    Test = _t
                ]
        ),
    #"Changed Type" =
        Table.TransformColumnTypes(
            Source,
            {
                {
                    "Index",
                    Int64.Type
                },
                {
                    "Test",
                    type text
                }
            }
        ),
    #"Added Custom" =
        Table.AddColumn(
            #"Changed Type",
            "Check",
            each
                Text.Contains(
                    Text.Combine(
                        List.Transform(
                            Text.ToList(Text.Lower([Test])),
                            each
                                if
                                    Character.ToNumber(_)
                                    >= 65345
                                    and Character.ToNumber(_)
                                    <= 65370
                                then
                                    Character.FromNumber(Character.ToNumber(_) - 65248)//offset between half and full width characters
                                else
                                    _
                        )
                    ),
                    "tesla"
                )
        )
in
    #"Added Custom"

Range of character list:

half-width characters(upper/lower): 65 ~ 90 / 97 ~ 122
full-width characters(upper/lower): 65313 ~ 65338 / 65345 ~ 65370

Sample character list:

let
    Source =
        List.Combine(
            {
                List.Numbers(
                    Character.ToNumber("A"),
                    26
                ),//half-upper
                List.Numbers(
                    Character.ToNumber("a"),
                    26
                ),//half-lower                
                List.Numbers(
                    Character.ToNumber("Ａ"),
                    26
                ),//full-upper
                List.Numbers(
                    Character.ToNumber("ａ"),
                    26
                )//full-lower
            }
        ),
    #"Converted to Table" =
        Table.FromList(
            Source,
            Splitter.SplitByNothing(),
            null,
            null,
            ExtraValues.Error
        ),
    #"Added Custom" =
        Table.AddColumn(
            #"Converted to Table",
            "Custom",
            each Character.FromNumber([Column1])
        ),
    #"Renamed Columns" =
        Table.RenameColumns(
            #"Added Custom",
            {
                {
                    "Column1",
                    "character number"
                },
                {
                    "Custom",
                    "character"
                }
            }
        )
in
    #"Renamed Columns"

BTW, if you work with huge amount of table records, you can also do transform on search text instead of raw field value:

#"Added Custom" =
        Table.AddColumn(
            #"Changed Type",
            "Check",
            each
                Text.Contains(
                    Text.Lower([Test]),
                    "tesla"
                )
                or Text.Contains(
                    Text.Lower([Test]),
                    Text.Combine(
                        List.Transform(
                            Text.ToList("tesla"),
                            each Character.FromNumber(Character.ToNumber(_) + 65248)
                        )
                    )
                )
        )

Regards,

Xiaoxin Sheng

Forum Discussion

Full-Width Characters? Power Query return value if contains certain text

5 Replies

Related content

If text doesn't contain character

Check if string contains certain characters

Contain Word

Visual calculation- Contain

Export data strange characters