

February 12, 2018

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?
Cheers.


November 8, 2013

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.


February 12, 2018

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!


November 8, 2013

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.


February 12, 2018

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!


February 12, 2018

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!


February 12, 2018

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!


February 12, 2018

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!
1 Guest(s)
