Forum Discussion
Replace Values in a Column bases on 2 other Columns
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 other issue is that Baseline is not an existing column, I just need that word to populate if those conditions are met.
Community ChampionTry 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
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.
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.