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"

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

If text, LEFT('Table'[Project ID],2) = "11" or if it is a number maybe LEFT('Table'[Project ID] & "",2)

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Greg

 

Thanks for the formula, now that I have looked back over the table there is actually bit more involved, my mistake for not realising there is also some key extra data.  There are other Project ID's that do not start with '11' but they need to be included as they are not legacy data/cases.   I just wanted to exclude cases thoses that started with '11' and status <> Active or more accuartly their end date; Dates_2, is in the future.

 

I did come up with a formula, based around what you posted, I broke my solution down into more manageable chunks to get it to work.  Probally not my best work:

 

New Column: LegacyChecker1 =
if(LEFT([Project_ID],2)="11", "Legacy", " ")
 
New Column: LegacyChecker2 =
if(
     and(
      [LegacyChecker1]="Legacy",
      [Dates_2]>=today()),
          "Legacy Live",
              if([LegacyChecker1]<>"Legacy", "Active Live")
)

 

I did come across some M Language for 'List.AllTrue'  and 'Text.StartsWith', I was tryiing combine these functions with IF, AND and OR to create a single calculation; that was the theory.

 

Chris