Convert a column of whole numbers into a column of sequential strings dynamically

[Anonymous]

Hi All,

I am working on a requirement where i have to mask a column data and display masked column name in place of it in the report and it should change dynamically. Example is shown as below: 

Original Column Name	Masked Column Name
Operator1	A
Operator2	B
Operator3	C

When more operators are added, masked column names whould be created dynamically.

E.g; For Operator4 --> D, Operator5 --> E etc.

I tried using conditional column in Power Query but it works if done manually but does not work dynamically as more and more operators are added. So, if there are 100 operators in future, 100 logical sequence of text should be created as masked column names. So, for Operator26 -- > Z, and for Operator27, it can be something like AA, Operator28 --> BB and so on.

I tried another approach where i extracted the numbers from Operator names and created a new column which is a list of numeric values. How can i convert the numeric column list of numbers to a sequential list of strings as Masked column ?

Any help is appreciated.

[BA_Pete · Accepted solution]

Hi @Anonymous ,

Try this as a new custom column. It will work up to 702 operators:

let
    __digit = Number.From(Text.Select([Original Column Name], {"0".."9"})),
    __round26 = Number.RoundDown((__digit - 1) / 26),
    __firstChar = if __digit < 27 then __digit + 64 else __round26 + 64,
    __secondChar = if __round26 > 0 then Number.Mod(__digit, 26 * __round26) + 64 else null
in
    Text.Combine(
        {
            Character.FromNumber(__firstChar),
            Character.FromNumber(__secondChar)
        }
    )

Example output:

Example working query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9ILUosyS8yVIrVQfCMUHjGqHJmqFxzFK6lJaqsgQEK38QIlW9mbobGRzXO3ADolFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Original Column Name" = _t]),
    addCharacters =
    Table.AddColumn(Source, "Characters", each
        let
            __digit = Number.From(Text.Select([Original Column Name], {"0".."9"})),
            __round26 = Number.RoundDown((__digit - 1) / 26),
            __firstChar = if __digit < 27 then __digit + 64 else __round26 + 64,
            __secondChar = if __round26 > 0 then Number.Mod(__digit, 26 * __round26) + 64 else null
        in
        Text.Combine(
            {
                Character.FromNumber(__firstChar),
                Character.FromNumber(__secondChar)
            }
        )
    )
in
    addCharacters

Pete

[AntrikshSharma · Accepted solution]

@Anonymous Try this:

let
    Source = 
        Table.FromColumns (
            { List.Transform ( { 1 .. 1000 }, each "Operator" & Text.From ( _ ) ) },
            type table [ Operator = text ]
        ),
    Replacements = 
        List.Zip ( { { "0" .. "9" }, { "A" .. "J" } } ),
    AddedCustom = 
        Table.AddColumn (
            Source,
            "Mask",
            each 
            Text.Combine (
                List.ReplaceMatchingItems (
                    Text.ToList ( Text.Select ( [Operator], { "0" .. "9" } ) ),
                    Replacements
                )
            ),
            type text
        )
in
    AddedCustom

[Anonymous]

Hi Antriksh, the solution works absolutely fine. Thank you.

[BA_Pete · Accepted solution]

Hi @Anonymous ,

Try this as a new custom column. It will work up to 702 operators:

let
    __digit = Number.From(Text.Select([Original Column Name], {"0".."9"})),
    __round26 = Number.RoundDown((__digit - 1) / 26),
    __firstChar = if __digit < 27 then __digit + 64 else __round26 + 64,
    __secondChar = if __round26 > 0 then Number.Mod(__digit, 26 * __round26) + 64 else null
in
    Text.Combine(
        {
            Character.FromNumber(__firstChar),
            Character.FromNumber(__secondChar)
        }
    )

Example output:

Example working query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9ILUosyS8yVIrVQfCMUHjGqHJmqFxzFK6lJaqsgQEK38QIlW9mbobGRzXO3ADolFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Original Column Name" = _t]),
    addCharacters =
    Table.AddColumn(Source, "Characters", each
        let
            __digit = Number.From(Text.Select([Original Column Name], {"0".."9"})),
            __round26 = Number.RoundDown((__digit - 1) / 26),
            __firstChar = if __digit < 27 then __digit + 64 else __round26 + 64,
            __secondChar = if __round26 > 0 then Number.Mod(__digit, 26 * __round26) + 64 else null
        in
        Text.Combine(
            {
                Character.FromNumber(__firstChar),
                Character.FromNumber(__secondChar)
            }
        )
    )
in
    addCharacters

Pete

[Anonymous]

Hi Pete, this is great. The solution worked fine. Thanks for helping me out.