Forum Discussion
Replace Values in a Column bases on 2 other Columns
I have 3 columns for this scenario: Timeframe, Period, and Application ID.
I need to replace any values in "Timeframe" to read "Baseline" -only if- "Period" is 7/11/2020 and "Application ID" is 24, 25, or 26.
Is there a formula I can use to do this in my Power BI table?
Hi Anonymous,
The formula given works (had to change [Application ID] in the forumla to [ApplicationId] as it appeared in the Excel file), and had to convert the date to Text per your original requirements, but I am wondering if the issue is the July 11 piece vs the year. One of your examples has 7/11/2019, and another 7/11/2020. So I changed it to handle any July 11 in the 21st century.
if Text.StartsWith([Period], "7/11/20") and [ApplicationId] >= 34 and [ApplicationId] <= 36 then "Baseline" else [Period])Then I added some fake 7/11/2020 data to your Excel table and I get this:
Works for 2019 and 2020.
Here is the file back with that working. If you truly only want July 11, 2019, then use:
if [Period] = "7/11/2019" and [ApplicationId] >= 34 and [ApplicationId] <= 36 then "Baseline" else [Period]and none of the 2020 dates will return the "Baseline" text.
Does that help?
10 Replies
- edhans
Community Champion
The easiest way is a multi-step approach in Power Query.
First, create a new column that has the answer you want. Add a new Custom Column with the following if/then/else construct:
= if [Period] = #date(2020,7,11) and [Application ID] >= 24 and [Application ID] <= 26 then [Baseline] else [Timeframe]This is your new Timeframe column, but it is probably just called Custom.
Now, remove your Timeframe column.
Rename your Custom column Timeframe.
Make sure to set the data type to the proper type instead of ABC/123. I don't know what type the data in there is.