none
how to whether the first character is number/alphabet in a column RRS feed

  • Question

  • hi all,

     i have a column with data

    1002967CATHEF

    GXHJKG

    56783KOTAK

    TURBO

    likewise

    i need to find whether first letter of the column is alphabet or number using DAX or in power query. Please help

    thanx in advance..


    lucky

    Tuesday, June 20, 2017 7:33 AM

Answers

  • Hi!

    With DAX you create a calculated column and write the formula:

    IsNumber = IFERROR(
                    ISNUMBER(VALUE(
                                LEFT(Tabela1[Column1];1)
                            )
                    );FALSE()
                )
    Result:

    With Power Query you will create a custom column and write:

    = Table.AddColumn(Fonte, "IsNumber", each Value.Is( Value.FromText( Text.Start([Coluna 1],1) ), type number))

    Add Custom Column

    Tuesday, June 20, 2017 1:40 PM
  • Another option for Power Query would be:

    = Value.FromText(Text.Start([Text],1)) is number

    Tuesday, June 20, 2017 6:55 PM

All replies

  • Hi!

    With DAX you create a calculated column and write the formula:

    IsNumber = IFERROR(
                    ISNUMBER(VALUE(
                                LEFT(Tabela1[Column1];1)
                            )
                    );FALSE()
                )
    Result:

    With Power Query you will create a custom column and write:

    = Table.AddColumn(Fonte, "IsNumber", each Value.Is( Value.FromText( Text.Start([Coluna 1],1) ), type number))

    Add Custom Column

    Tuesday, June 20, 2017 1:40 PM
  • Another option for Power Query would be:

    = Value.FromText(Text.Start([Text],1)) is number

    Tuesday, June 20, 2017 6:55 PM
  • how can i modify this to know if the first 9 characters are number or not
    Saturday, June 23, 2018 6:16 PM