Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, I'm very new to Power BI and learning now M language.
My question is: which formula would remove a specific character in a table?
For example, I have this code:
let
Source = Excel.Workbook(File.Contents("my_path.customer.xlsx"), null, true),
Categories_Sheet = Source{[Item="Categories",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Categories_Sheet),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"CategoryID", Int64.Type}, {"CategoryName", type text}, {"Description", type text}, {"Link", type text}}),
Custom2 = Text.Remove(#"Changed Type".[CategoryName],{"o"})
in
Custom2
And I want to remove all "o"s from CategoryName column, so I've written this line:
Custom2 = Text.Remove(#"Changed Type".[CategoryName],{"o"})
but its' not correct.
Solved! Go to Solution.
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.
Proud to be a Super User!
Hi everyone,
For those that complained about the question instead of providing an appropiate answer, next time please just not comment.
If you say the functions are not built for certain task you better assume you just not know enought instead.
Here is the line of code you were probably looking for mate:
Custom2 = Table.TransformColumns(#"ChangedType", {{ "CategoryName", each Text.Remove(_,"o") }} )
*Note this is case sensitive. The easiest way to go around this is to repeat the same line of code changing the o by an O or whatever character you want to remove (there are other more sofisticated ways to do it, of course, but sure you'll find them in the future).
Keep the learning going @linoybar!
To replace "o" and "O" in one go:
Custom2 = Table.TransformColumns(#"ChangedType", {{ "CategoryName", each Text.Remove(_,{"o", "O"}) }})
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:
Proud to be a Super User!
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.
Proud to be a Super User!
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.
Proud to be a Super User!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |