Forum Discussion
Calculate a date based on text value
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.
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?
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.
