Replace Values in a Column bases on 2 other Columns

[Anonymous]

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?

[edhans]

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. 

[Anonymous]

Thanks Edhans. That Period field showing the date is actually set as "Text." Should the formula be adjusted for that? It is a pre-existing dashboard that has been around since before my time and I dont want to change the format in case it has an impact on other things.

[Anonymous]

The other issue is that Baseline is not an existing column, I just need that word to populate if those conditions are met.

[edhans]

Try this @Anonymous 

= if [Period] = "7/11/2020" and [Application ID] >= 24 and [Application ID] <= 26 then "Baseline" else [Timeframe]

If you need addional help, please post some data. You are using quotes for text and column names so it is hard to know what is a string and what is a column, and I couldn't tell the date was not a date either. You can add leading zeros to the 7 as needed if it is just a string.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum 

[Anonymous]

Edhans....thanks again. For some reason after entering the formula in a new Custom column, it does not change the respective values to baseline. I have attached my onedrive link for this file, I appreciate if you can take a look. It is a mock example of the real version which I cannot upload. The only columns that truly matter are Period and Application ID. Thanks for any adivce.

Please see custom column on the attached with the latest formula you provided.https://1drv.ms/u/s!Ag9Skg4a2hk_kB01UHKCUgxmU_vp?e=WsVTgg 

[edhans]

I cannot use this. You didn't supply the related Excel file so I see nothing but this. Looking at the Table in the data model doesn't elp much, other than the custom column seems to work for July 11, 2019, but not 2020.

It also depends on your computer settings. Your Period column comes in as a date, then you convert to text, but it was after the date conversion. You should remove the 2nd Changed Type step and go back to the first one, then tell it to change Period to text if that is what you want. Answer Yes to "insert a step" and "replace" the current step. That way, it never converts to a date to begin with.

Other than that, I need to see the real data.

[Anonymous]

https://1drv.ms/x/s!Ag9Skg4a2hk_kB64ylODUhyRSWRG?e=YJ6rYq 

I attached a new link to the actual Excel file I am using. It's just two columns (Period and ApplicationID), the third would be the custom column to be created in Power BI.

I modified your formula for what I actually need for this new file and placed it on the 2nd tab of the Excel file.

I simply need to upload that dataset in Power BI, and use your formula to create the 3rd column. If this isn't enough on your end, I wont take up anymore of your time. I appreciate the help thus far. See modified formula on 2nd tab.

[edhans · Accepted solution]

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? 

The Excel file.

[Anonymous]

Edhans-----thanks, you have been a great help!

[edhans]

Glad to help @Anonymous !