Forum Discussion
m query remove character from column in a table
Text.Remove doesn't take a column as the input. It takes a text value. Read the documentation on it. It's not meant to operate on full columns. It's meant to operate on a single text value. I can't tell you how to use Text.Remove on a column because that's not what that function is for. It is not the correct function to use in order to reach the outcome you want. Wrong tool for the job. And by the way, ASKING THE SAME QUESTION A SECOND TIME IN ALL CAPS DOESN'T CHANGE THE ANSWER.
If you're hellbent on using that particular function for some reason, I suppose you could use it in a new column definition formula where you copy the text values from each row in another column, then use Text.Remove to remove those letters. But that leaves you with two columns, one with o's, one without. So you'll probably find that answer unacceptable too.
If you want to reference a column in M code, you have to use a formula that takes a column as an argument. Perhaps something like Table.ReplaceValue...
The interface option, as you call it, just generates M code. You can use the Replace Values button or you can type this code by hand. They are literally the same thing, except one of them takes longer to do.
Use the "Replace Values" button.
Enter o as the value to replace. Leave the second box blank; don't enter anything to replace it with.
Replace Values is case sensitive, so you'll need to do the same thing a second time but with a capital O, otherwise you'll be left with this:
Yeah, I'm aware of that interface option.
I'm just trying to figure out how i can reference a COLUMN IN A TABLE in M code.
this code line is not correct: Custom2 = Text.Remove(#"Changed Type".[CategoryName],{"o"})
If my column is "categoryName", how should I write a reference to it in a TextRemove function?
Or maybe it's unavailable to use this function at all with table columns?
Thanks
Text.Remove doesn't take a column as the input. It takes a text value. Read the documentation on it. It's not meant to operate on full columns. It's meant to operate on a single text value. I can't tell you how to use Text.Remove on a column because that's not what that function is for. It is not the correct function to use in order to reach the outcome you want. Wrong tool for the job. And by the way, ASKING THE SAME QUESTION A SECOND TIME IN ALL CAPS DOESN'T CHANGE THE ANSWER.
If you're hellbent on using that particular function for some reason, I suppose you could use it in a new column definition formula where you copy the text values from each row in another column, then use Text.Remove to remove those letters. But that leaves you with two columns, one with o's, one without. So you'll probably find that answer unacceptable too.
If you want to reference a column in M code, you have to use a formula that takes a column as an argument. Perhaps something like Table.ReplaceValue...
The interface option, as you call it, just generates M code. You can use the Replace Values button or you can type this code by hand. They are literally the same thing, except one of them takes longer to do.
OK, I get it now!
The function I've chosen didn't get a column name as an argument...
Thank you very much for the clarification and sorry for the caps, I just was struggling with this thing for a couple of days now :)
It's always dangerous to get hung up on a particular formula. If you didn't pick the right formula you can waste a lot of time and energy. I've found that it's best to start by thinking of the most generalized statement of the behavior you want and use that to search through the documentation for the right method. In this case what you want is to change part of the contents of an entire column without creating a new column. In M you're working either with a single value, a list, or a table*. You have to pick a formula that takes whatever of those 3 things you're working with as an input. A column is not the same as a list, and it's definitely not a single value, so the thing you want to do must operate at a table level. So the Table functions are where you should start looking. Text formulas are pretty much all single value or list.
Alternative way of thinking about the problem: don't put too much stock in the names of formulas. Programmers do their best but the names don't always illustrate how they can be applied. Also, always remember that blank or null is also a valid value. So "delete a value" is identical to "replace a value with nothing".
*Or a record. But you could think of a record as a single value that contains either a single value, a list, or a table, so ignore it for now.