Forum Discussion
Calculate a date based on text value
Hi everyone. New to power query here. Wondering if you can help me with this.
I have a table containing some data regarding tasks, and would like to calculate a date based on the urgency rating.
I have two columns:
- Date submitted
- Urgency (which can contain the values: Not urgent, Fairly Urgent, Very Urgent)
I've decided that:
- not urgent should have a deadline of a month
- fairly urgent should have a deadline of 2.5 weeks
- very urgent should have a deadline of 7 days
How do I go about creating this column with the respective dates?
6 Replies
Here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below. Note that Power Query is case sensitive, so you can wrap in Text.Lower if you expect values of different case.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJW0lHKyy9RKC1KT80rUYrVAckYwWTSEjOLcipRJY1hkmWpRQipWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date Submitted" = _t, Urgency = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Submitted", type date}, {"Urgency", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Deadline", each if [Urgency] = "not urgent" then Date.AddMonths([Date Submitted], 1) else if [Urgency] = "fairly urgent" then Date.AddDays([Date Submitted], 17) else Date.AddDays([Date Submitted], 7)) in #"Added Custom"Pat
Is there any way to do it just by adding a custom column?
What do you think is being done? It appears to me as if adding a custom column is all that is happening.
