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?
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.
Community ChampionHi 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?
Edhans-----thanks, you have been a great help!
- edhans
Glad to help Anonymous !