Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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?

1 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:

edhans_0-1601999255079.png

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

10 REPLIES 10
edhans
Super User
Super User

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. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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
Not applicable

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

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 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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 

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.

edhans_1-1601940429749.png

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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.

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:

edhans_0-1601999255079.png

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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

Glad to help @Anonymous !

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors