Replace value in one column based on date in another

[thechad13]

Hello...

I am currently in need of replacing specific values in a column if the current date is prior to a date I specify and leaving them as is after that date. Unfortunately my coding knowledge is limited at best. 

For example, in column "Badge", if the current date is prior to April 1, 2024, I would like to replace all badges that are 837 with # 1013. If the current date is April 1, 2024 or later I want to leave the badge # as is. There may be multiple badge #'s that this may apply to but these will need to be changed manually and added as needed. I think if I have at least one viable example that works I should be able to figure out how to add additional cases. 

Thanks in advance for any assistance!

C

[jgeddes]

This is pretty easy to do in Power Query. 
Example

If you want all Badges equal to 837 before April 1, 2024 to be 1013 you can use the 'Replace Values' button on the Transform ribbon.

Fill the dialog box like follows and click ok.

The resulting formula will look like...

Change the 1013 so it looks like...

and you end up with...

Now if you have multiple badges to replace you can create a second table that looks like...

and then write a function into the query that returns the replacement badge when input date is less than the threshold date for the current badge.
That code would look like this...

let
// static data table source, you would replace this with the current source you have...
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjA2V9JRMjDWNzbUNzIwMlGK1YEJGusbmaOLmegbYKgDihkhxCwtLTENhAoiqYwFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Badge = _t, Date = _t]),
// changes column data types to match expected
    #"Changed Type" = 
    Table.TransformColumnTypes(
        Source,
        {
            {"Badge", Int64.Type}, 
            {"Date", type date}
        }
    ),
// custom function that takes the badge number and date from the main table as inputs and selects the row in the replacement table that matches the criteria
    fxReplacmentBadge = (inputBadge as number, inputDate as date) =>
    let
       value = 
       Table.FirstValue(
           Table.SelectColumns(
               Table.SelectRows(
                   replacementBadges, 
                   each [Lookup Badge] = inputBadge and [Date Threshold] > inputDate
                ), {"Replacement Badge"}
            )
        ) 
    in 
        value,
// replace the badge value in the main table if the replacement value is not null
    Custom1 = 
    Table.ReplaceValue(
        #"Changed Type", 
        each [Badge], 
        each if fxReplacmentBadge([Badge], [Date]) = null then [Badge] else fxReplacmentBadge([Badge], [Date]), 
        Replacer.ReplaceValue, 
        {"Badge"}
    )
in
    Custom1

The output for this code would be...

[thechad13]

Thanks for this jgeddes...there aren't many circumstances where I need to replace more than a few badge #s at any given time. I would prefer to stay away from creating any additional tables or columns and have the before date built into the formula itself. 

Is it possible to take the formula in your example (

= Table.ReplaceValue(#”Changed Type”, 837, 1013, Replacer.ReplaceValue,{“Badge”})) and add before date coding within the formula itself? If I have more than one badge that needs changing at any given time I can simply duplicate the formula, change the badge numbers and the date (if needed). 

Is this something that can be done. This approach would be more inline with my needs. 

Thanks!

[jgeddes]

Are you replacing multiple badge numbers with the same badge number and the same before date?

[thechad13]

I would only replace matching badge #s with a new badge #s in special circumstances (i.e. 837 = 1013, 901 = 1111, etc.) but the before date will always be before April 1. As I think about it now , the date range would be April 1, 2023 to March 31, 2024 (last year's performance year for the badge #s). 

I hope that makes sense. 

Thanks.

[jgeddes]

You can repeat the first solution I provided as many times as you want. Just repeat the steps and change the resulting code to match your values.
Eg. 837 to 1013 goes from

= Table.ReplaceValue(#”Changed Type”, 837, 1013, Replacer.ReplaceValue,{“Badge”}))

to 

= Table.ReplaceValue(#”Changed Type”, 837, each if [Date] < #date(2024,04,01) then 1013 else [Badge], Replacer.ReplaceValue,{“Badge”}))

901 to 1111 would be 

= Table.ReplaceValue(#”Replaced Value”, 901, 1111, Replacer.ReplaceValue,{“Badge”}))

to 

= Table.ReplaceValue(#"Replaced Value",901,each if [Date] < #date(2024,04,01) then 1111 else [Badge],Replacer.ReplaceValue,{"Badge"})

the values #"Changed Type" and #"Replaced Value" are the previous steps in the query so they will change along with values you enter.
In this example #"Replaced Value" will show up automatically if you change the second badge in the step right after the change of the first badge.

[thechad13]

When I add a step to insert the code you have provided above, the code shows this: = #"Renamed Columns17" (which  somehow points to the previous added step)

If I delete = #"Renamed Columns17" and insert your code, Query generates an error: Expression.SyntaxError: Invalid literal.

Coding is not my strength so I'm not sure how to keep = #"Renamed Columns17" and tie in your code from above. I'm sure it's a simple solution but not when you don't understand all the coding language! ☹️

Thanks

[jgeddes]

What is the name of the last step in your query (before adding my code)?

[thechad13]

Renamed Columns17

[jgeddes · Accepted solution]

Another way to enter these is to right click on your last step (#"Removed Value17")  in the Applied Steps box and select 'Insert Step After'. 

The formula field will then look like...

You can then paste in...

Table.ReplaceValue(#"Replaced Value17", 837, each if [Date] < #date(2024,04,01) then 1013 else [Badge], Replacer.ReplaceValue,{"Badge"})

Repeat that process, this time right clicking on the #"Replaced Value18" step and pasting in...

Table.ReplaceValue(#"Replaced Value18", 901, each if [Date] < #date(2024,04,01) then 1111 else [Badge], Replacer.ReplaceValue,{"Badge"})

If you are getting the 'Invalid Literal' error, make sure the quote marks in your code match the quote marks for your language.
Hopefully one of these methods works for you.

[thechad13]

Would I leave this part of the code that shows up when I insert new step or delete it and add your code above? 

= #"Renamed Columns17"

Not trying to test your patience!

[thechad13]

Ignore the above question...I figured it out but a new problem popped. A whole bunch of my visuals connected to the table I'm working are now broken and I have no idea why. 

Your code definitely worked and thank you for that. Not sure how the new issues are connected but will see if I can spot an issue. 

[jgeddes]

Glad it finally worked. If you cannot figure out the errors in your visuals feel free to send me a private message and I can help you troubleshoot the errors you are seeing.

[thechad13]

Went to bed last night and started thinking that I may have already used a Replaced Value17 somewhere in previous steps. Checked today and sure enough that was the case. Changed the Replaced Value and sure enough it resolved the issue!

Thanks again for all your help and patience!!!

C

[jgeddes]

Ok. 

If you are adding the code directly in the advanced editor the code should look like..

#"Replaced Value18" = Table.ReplaceValue(#”Replaced Value17”, 837, each if [Date] < #date(2024,04,01) then 1013 else [Badge], Replacer.ReplaceValue,{“Badge”})),
#Replaced Value19" = Table.ReplaceValue(#”Replaced Value18”, 901, each if [Date] < #date(2024,04,01) then 1111 else [Badge], Replacer.ReplaceValue,{“Badge”}))
in
#"Replaced Value19"