Log In
Register
Home
February 12, 2018
Offline
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
Offline
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
Offline
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
Offline
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
Offline
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
Offline
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]
February 12, 2018
Offline
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!
November 8, 2013
Offline
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]
February 12, 2018
Offline
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!
November 8, 2013
Offline
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 🙂
February 12, 2018
Offline
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!
November 8, 2013
Offline
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.
1 Guest(s)
