Code for Power Query

[Anonymous]

Hello All

I started working a task by creating calculated columns and it's causing problems with other steps due to circular references, etc. I would like to start over and just fix everything by doing it in Power Query. Here is the scenario:

I have a Column called "Period" which has dates. I need to replace certain dates in this column with the word "Baseline." Condition has to be met in another column for this to happen. It goes like this:

If the "ApplicationID" column has 34, 35, or 36 and the "Period" column has the date "7/11/2019" then change the "7/11/2019" to say "Baseline." That's it----that's all I need.

Is there an M Code that can do this in Power Query. Remember that I have to make this change in the Period Column. I dont want to create another column becuase this report is big and many queries already exist. I am just doing this as a band-aid at the very last step.

Extremely grateful for any help!

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjZR0lEy1zc01DcyMLRUitUBCplChIyQhMwwVZljqrJAFYoFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ApplicationID = _t, Period = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ApplicationID", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","7/11/2019",each if [ApplicationID] = 34 or [ApplicationID] =35 or [ApplicationID] =36 then "Baseline" else [Period],Replacer.ReplaceText,{"Period"})
in
    #"Replaced Value"

[Anonymous · Accepted solution]

Thanks --- I was finally able to achieve it this way:

= Table.ReplaceValue(#"Changed Type",each [Period], each if [Period]="7/11/2019" and [ApplicationId]>=34 and [ApplicationId]<=36 then "Baseline" else [Period],Replacer.ReplaceText,{"Period"})