Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Perform a test, read the the first 2 digits from a numeric field.

Hi   I trying to create a custom column based on how the value in column  'Project_ID' begins.  The Project_ID is a whole number, it does not contain any text values.  I wanted to flag any projects...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi Anonymous ,

     

    We can create a custom column like that to work on it.

    if Text.StartsWith(Number.ToText([Project ID]),"11") and [Status] = "Closed" then "Legacy" else null

     

    M code for your reference.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0MjYxNVfSUXJMLsksS1WK1QEJmpqamGAIGptbWIIEnXPyi1NToIImQEFLJMFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project ID", Int64.Type}, {"Status", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "LegacyChecker", each if Text.StartsWith(Number.ToText([Project ID]),"11") and [Status] = "Closed" then "Legacy" else null)
in
    #"Added Custom"

     

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous ,

 

We can create a custom column like that to work on it.

if Text.StartsWith(Number.ToText([Project ID]),"11") and [Status] = "Closed" then "Legacy" else null

 

M code for your reference.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0MjYxNVfSUXJMLsksS1WK1QEJmpqamGAIGptbWIIEnXPyi1NToIImQEFLJMFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project ID", Int64.Type}, {"Status", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "LegacyChecker", each if Text.StartsWith(Number.ToText([Project ID]),"11") and [Status] = "Closed" then "Legacy" else null)
in
    #"Added Custom"

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Frank

     

    Thats great thanks for your help.

     

    Chris