Search code examples
powerbipowerquerym

Text.Trim Syntax Meaning

I'm working in a Power BI query, trying to trim whitespace from text.

Looking at Microsoft's M reference, I came across the Text.Trim syntax:

Text.Trim(text as nullable text, optional trimChars as any) as nullable text

I couldn't figure out how to plug it into my query code correctly (where it would actually work) so I did some more searching and came across this:

#"Trimmed Text" = Table.TransformColumns(#"Removed Other Columns",{},Text.Trim),

...which doesn't look anything like Microsoft's syntax but works fine for me as I insert it into my code like this:

let
    Source = Banding,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Segment", "Granular Band"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Other Columns",{},Text.Trim),
    #"Removed Duplicates" = Table.Distinct(#"Trimmed Text", {"Granular Band"})
in
    #"Removed Duplicates"

My problem is that I don't understand what the line's syntax meaning is. I understood Microsoft's example as meaning basically, trim THIS; where THIS is the text I want trimmed. Pretty straightforward.

But I don't know what the syntax meaning of the line that actually works is. I understand that it says to transform table columns (Table.TransformColumns); but I don't know if the reference to the previous line (#"Removed Other Columns") serves as any real "input" for the Text.Trim or if {} is a reference to all columns in the table, or (more importantly to me) how I would reference specific columns. (I've tried a few approaches for specifying columns and failed every time.) I also don't understand why I don't need any arguments following Text.Trim (like in Microsoft's example).

If someone would translate what the line is "saying" in a manner I can understand, I'd sure appreciate it.

Solution

  • The generated code:

    #"Trimmed Text" = Table.TransformColumns(#"Removed Other Columns",{},Text.Trim),

    means that you most probably selected all columns and then you selected Transform - Format - Trim. If you would have selected 1 or more columns, then the names of these columns and the required operations would have been between the {}, like in

    = Table.TransformColumns(#"Removed Other Columns",{{"SomeText", Text.Trim}})

    Any function that is invoked within Table.TransformColumns, gets the column values automatically supplied from Table.TransformColumns, so in this case: the first argument for Text.Trim (text as nullable text). The other arguments will use the default value, i.c. space, so by default all leading and trailing spaces are removed.

    If you want to use other arguments of Text.Trim within the Table.TransformColumns context, then you need to adjust the code and supply the keyword "each", and use an _ as placeholder for the column values. For example the next code removes leading and trailing spaces and semicolons:

    = Table.TransformColumns(#"Removed Other Columns",{{"SomeText", each Text.Trim(_, {" ",";"})}})