Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
linoybar
Frequent Visitor

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.

1 ACCEPTED 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...

 

TableReplaceValue.PNG

 

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

9 REPLIES 9
NAOS
Helper IV
Helper IV

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"}) }})

 

linoybar
Frequent Visitor

Cool! Thanks Naos, I'll try your solution.
KHorseman
Community Champion
Community Champion

Use the "Replace Values" button.

 

Replace.PNG

 

ReplaceValues.PNG

 

Enter o as the value to replace. Leave the second box blank; don't enter anything to replace it with.

 

Replaceo.PNG

 

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:

Deletedo.PNG





Did I answer your question? Mark my post as a solution!

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

 

TableReplaceValue.PNG

 

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.





Did I answer your question? Mark my post as a solution!

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sean
Community Champion
Community Champion

I have to say @KHorseman's posts are by far the most fun to read! Smiley Very Happy

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.