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

[ccarpent]

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 that are from our legancy system, projects from the old system begin with '11' plus I need to test if a few of them are still active but checking a field called 'Status'; which is a text field, shows these jobs are 'Active' or not; as smal number are still valid.    So if I can indentify which project is from the Legacy system I will populate a custom column; e.g. LegacyChecker, with the value 'Legacy'.  I was then planninng to then filter out thoese that have a value of 'Legacy'. The conditional formating in powerquery has 'begins with', but this only applies to text.  I am wanting to do this work at the powerquery or Query Editor stage rather then the front end; but will take advise on best practice.

Example data:

Project ID      Status    LegacyChecker

1123457        Active      

1155447        Active

1137897        Closed     Legacy

1147899        Closed     Legacy

Many thanks

Chris

[v-frfei-msft · Accepted solution]

Hi @ccarpent ,

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"

[ccarpent]

Frank

Thats great thanks for your help.

Chris

[Greg_Deckler]

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

[ccarpent]

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