Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 2...
Need Help
  • edhans's avatar
    edhans
    6 years ago

    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.

     

edhans's avatar
edhans
Icon for Community Champion rankCommunity Champion
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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

      • edhans's avatar
        edhans
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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