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.

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!

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.

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!

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]

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!

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]

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:

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!

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 🙂

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!

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.

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