Forum Discussion
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 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
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 nullM 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"
4 Replies
- Greg_Deckler
Community Champion
If text, LEFT('Table'[Project ID],2) = "11" or if it is a number maybe LEFT('Table'[Project ID] & "",2)
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
v-frfei-msftCommunity Support
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 nullM 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"
