• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Formula for Replacing a Value in One Column Based on the value in a Different Column|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Formula for Replacing a Value in One Column Based on the value in a Different Column|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryFormula for Replacing a Value in On…
sp_PrintTopic sp_TopicIcon
Formula for Replacing a Value in One Column Based on the value in a Different Column
Avatar
Tyler
Upstate New York
Member
Members
Level 0
Forum Posts: 34
Member Since:
February 12, 2018
sp_UserOfflineSmall Offline
1
February 12, 2018 - 1:13 pm
sp_Permalink sp_Print

I'm sure the solution is simple but I can't seem to crack this. The desired result is this:

I am using Group By to sum amounts, but the column Finance Code MUST remain, however, the Finance Codes are different in the 2 rows I need to group, and I need them both to say 2000I in order for the Group By to work.

The caveat: I've left out about 150 rows for simplicity-sake, but there are many other rows where Finance Code column must remain 2020, therefore I cannot do a simple Replace Values because it would change every instance of 2020 to 2000I, which is not what I'm looking for; I need only to make this replacement for 3 total rows.

Below is a screenshot. Anyone have any suggestions? 

PQ-Shot.pngImage Enlarger

 

Cheers.

sp_PlupAttachments Attachments
  • sp_PlupImage PQ-Shot.png (13 KB)
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
February 12, 2018 - 5:12 pm
sp_Permalink sp_Print

Hi,

Can you upload a sample file?

There should be a criteria for these replacements, your message is not describing the logic that needs to be applied in order to keep only a few codes unchanged and replace the rest. If you know the logic, you can add a new conditional column with a formula that can replace the finance code under certain circumstances, the apply the grouping based on this new column.

Avatar
Tyler
Upstate New York
Member
Members
Level 0
Forum Posts: 34
Member Since:
February 12, 2018
sp_UserOfflineSmall Offline
3
February 12, 2018 - 11:48 pm
sp_Permalink sp_Print

OK here is a sample file. As far as the logic goes, that's what I don't have experience writing, but if I had to take a stab, it would go something like this:

If ID# = 95053 and 96354 and 96481 then Finance Code = 2000I else Finance Codes stay as is in the rest of the ID#s. Sorry about that bold type, I realize it's not proper code but in English, that is the best way I could describe it.

As you can see, I've already grouped and summed by amount, but there are 3 ID#s that are duplicated because the Finance Codes are not matching: ID#s 95053, 96354 & 96481 (rows 101-102 & 107-110). I've highlighted the records in question in yellow, orange and red.

Hope this helps.

Thanks!

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
February 13, 2018 - 2:17 am
sp_Permalink sp_Print sp_EditHistory

Here is a step you can add before grouping:

= Table.AddColumn(Source, "New Finance Code", each if Text.Contains("95053, 96354, 96481",[#"ID #"]) then "2000I" else [Finance Code])

the formula for this step is simple:

= if Text.Contains("95053, 96354, 96481",[#"ID #"]) then "2000I" else [Finance Code]

The you will be able to group using this new column instead of the old Finance code column.

Avatar
Tyler
Upstate New York
Member
Members
Level 0
Forum Posts: 34
Member Since:
February 12, 2018
sp_UserOfflineSmall Offline
5
February 13, 2018 - 4:41 am
sp_Permalink sp_Print

I inserted a step before the Grouped Rows step by got the following error: 

Expression.Error: We cannot convert the value null to type Text.
Details:
Value=
Type=Type

Sorry, I forgot to tell you that not all the records have Finance Codes, some have null values. Is there a workaround for this?

Thanks!

Avatar
Tyler
Upstate New York
Member
Members
Level 0
Forum Posts: 34
Member Since:
February 12, 2018
sp_UserOfflineSmall Offline
6
February 13, 2018 - 4:52 am
sp_Permalink sp_Print

By the way, my process was as follows: the step before the Grouped Rows step, I added a custom column and pasted your formula you gave me: = if Text.Contains("95053, 96354, 96481",[#"ID #"]) then "2000I" else [Finance Code]

Avatar
Tyler
Upstate New York
Member
Members
Level 0
Forum Posts: 34
Member Since:
February 12, 2018
sp_UserOfflineSmall Offline
7
February 13, 2018 - 1:23 pm
sp_Permalink sp_Print

Thanks so much for your help, Catalin. You set me on the right path. To solve, I replaced ID# null values with a hyphen. I was previously mistaken saying that not all records have a Finance code, they do; I meant to say that not all records have a ID#, that's where the null values were throwing of the grouping.

Cheers!

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
8
February 13, 2018 - 3:18 pm
sp_Permalink sp_Print

You can handle null values in formula:

= if [#"ID #"]<> null then (if Text.Contains("95053, 96354, 96481",[#"ID #"]) then "2000I" else [Finance Code]) else [Finance Code]

Avatar
Tyler
Upstate New York
Member
Members
Level 0
Forum Posts: 34
Member Since:
February 12, 2018
sp_UserOfflineSmall Offline
9
February 13, 2018 - 11:31 pm
sp_Permalink sp_Print

Thanks, I'll give that try shortly. However, I have run into another error. I've decided I want to bring in some additional columns that were previously left out of the grouping, and the situation is very similar as the original problem. I have 2 rows that are identical EXCEPT for a value in one of the columns, and in order to group and sum the records, I need to replace a value in one cell based on the value of a cell in a different column. I used the same logic you previously supplied, and changed the values, but I'm getting the following error:

ADD-Custom-Column-Error.pngImage Enlarger

I'll upload the file soon, but any ideas so far based off my screenshot as to why it is giving me the Invalid Identifier error. It doesn't seem to like the name of the column in brackets, Related Transactions Line No.

Thanks!

sp_PlupAttachments Attachments
  • sp_PlupImage ADD-Custom-Column-Error.png (22 KB)
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
10
February 13, 2018 - 11:54 pm
sp_Permalink sp_Print

Always select the column from the right side list of columns and double click on it to use it in formula. You'll see the difference. You can even see the correct syntax in the first part of the formula where you refer to the ID column, you just have to look there 🙂

Avatar
Tyler
Upstate New York
Member
Members
Level 0
Forum Posts: 34
Member Since:
February 12, 2018
sp_UserOfflineSmall Offline
11
February 14, 2018 - 11:38 pm
sp_Permalink sp_Print

It worked, thank you! I see that after I selected the column from the right side list of columns and double clicked on it, a # symbol was added before Related Transactions Line No.

Interesting note however: your earlier formula suggestion did not use a # symbol in front of Finance Code and the syntax was accepted. I even selected the column from the right side list of columns and double clicked on it, but this time a # symbol was NOT added before Finance Code, but it still worked. Any idea why this might be?'

Thanks!

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
12
February 15, 2018 - 9:49 pm
sp_Permalink sp_Print

If the column name contains special chars that may break the M query language, the header is converted to a text string with this pattern: #"Header text"

Your headers have a dot, or the # symbol in the column name. The ones without special chars will not be converted.

Avatar
Tyler
Upstate New York
Member
Members
Level 0
Forum Posts: 34
Member Since:
February 12, 2018
sp_UserOfflineSmall Offline
13
February 15, 2018 - 11:40 pm
sp_Permalink sp_Print

Thank you so much for the explanation, I've learned so much from this post. You Admins on this forum are incredible.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Shanna Getschel, andria young, Bruce Tang Nian
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 205
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Bruce Tang Nian
Scot C
Othman AL MUTAIRI
Misael Gutierrez Sr.
Attif Ihsan
Kieran Fee
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Bhuwan Devkota
Forum Stats:
Groups: 3
Forums: 24
Topics: 6222
Posts: 27293

 

Member Stats:
Guest Posters: 49
Members: 31918
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.