Forum Discussion
m query remove character from column in a table
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.
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.
9 Replies
NAOSAdvocate III
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!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.
