Calculate a date based on text value

[midlandjoe]

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?

[ppm1]

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

[midlandjoe]

Is there any way to do it just by adding a custom column?

[ronrsnfld]

What do you think is being done? It appears to me as if adding a custom column is all that is happening.

[midlandjoe]

Sorry, I guess I'm just a little confused -- as I already have quite a few different columns in my table. I'm also sourcing the data from Sharepoint so my advanced query editor is a little harder to edit. 

Is there another way that this can be achieved without adding / editing the query?

[ppm1]

The provided M code is just an example. You can just add a custom column in your existing query and put this formula in the pop-up box.

= 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)

Pat

[ronrsnfld]

Why would you expect the outcome of a query to change if you don't change the query? Whether you accomplish this in the Advanced Editor, or in the Power Query User Interface is irrelevant.

When you followed the suggestion put forth by @ppm1 and clicked on the #"Added Custom" step, what did you see?  That is the formula to use if you are changing the query using the UI.