Error in Trimming a Column

[Anonymous]

Hi, 

I'm working with a data imported from SQL database then to PowerBI. It's a direct import from power BI as we are not allowed to do anything through the MS SQLserver management studio

So I have imported the necessary data that I have and realized that I need to trim one important column in power query. For reference, here is the script from advanced editor: 

let
    Source = Sql.Database("hidden.sql.azuresynapse.net", "hidden"),
    GL_Transaction = Source{[Schema="GL",Item="Transaction"]}[Data],
    #"Filtered Rows" = Table.SelectRows(GL_Transaction, each [#"G/L Date"] >= #date(2022, 11, 1)),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [#"G/L Date"] >= #date(2022, 11, 1) and [#"G/L Date"] <= #date(2024, 5, 31)),
    #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each ([Doc Type Description] = "Entries")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows2",{{"Business Unit ", "Job/Project No."}, {"Explanation Alpha Name", "Description"}, {"Explanation -Remark-", "Additional Description"}}),
    #"Filtered Rows3" = Table.SelectRows(#"Renamed Columns", each ([Entity Group] <> "TPL")),
    #"Trimmed Text" = Table.TransformColumns(#"Filtered Rows3",{{"Account Description", Text.Trim, type text}})
in
    #"Trimmed Text"

 However power query gave me this error: 

DataSource.Error: Microsoft SQL: The trim function requires 1 argument(s).
Statement ID: {2807ABB9-FA52-4A99-A876-1F27E9E71807} | Query hash: 0x49E7CEC2A2EA0828 | Distributed request ID: {479C14A9-50BD-4BB5-82F4-EE7DEB2A708F}. Total size of data scanned is 11 megabytes, total size of data moved is 2 megabytes, total size of data written is 0 megabytes.
Details:
    DataSourceKind=SQL
    DataSourcePath=hidden
    Message=The trim function requires 1 argument(s).
etc.

Can anyone help me discover what the issue is? Or is there any alternative I can do to rectify this one? 

I tried duplicating the column and doing the trimming but it gave me the same error. 

Thank you so much! 

[Anonymous]

Hi @Anonymous ,

There seems to be no problem with the code. Could you try the code below and let me know the result?

let
    Source = Sql.Database("hidden.sql.azuresynapse.net", "hidden"),
    GL_Transaction = Source{[Schema="GL",Item="Transaction"]}[Data],
    #"Filtered Rows" = Table.SelectRows(GL_Transaction, each [#"G/L Date"] >= #date(2022, 11, 1) and [#"G/L Date"] <= #date(2024, 5, 31) and ([Doc Type Description] = "Entries") and ([Entity Group] <> "TPL")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Business Unit ", "Job/Project No."}, {"Explanation Alpha Name", "Description"}, {"Explanation -Remark-", "Additional Description"}}),
    #"Trimmed Text" = Table.TransformColumns(#"Renamed Columns",{{"Account Description",each Text.Trim(_), type text}})
in
    #"Trimmed Text"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[Anonymous]

Hi, tried the script above but it did not work as well 😞

Thanks for helping!

[Smalfly]

Hi @Anonymous ,

could it be that you are pulling data from your SQL database that has blank values "" instead of null values?

If so, you can put this statement before your trim step:

= Table.ReplaceValue(#"FilteredRows3","",null,Replacer.ReplaceValue,{"Account Description"})

[Anonymous]

I've done it as well, adding these lines on the last working steps 

    #"Filtered Rows3" = Table.SelectRows(#"Renamed Columns", each ([Entity Group] <> "TPL")),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows3","",null,Replacer.ReplaceValue,{"Account Description"}),
    #"Trimmed Text" = Table.TransformColumns(#"Replaced Value",{{"Account Description", Text.Trim, type text}})
in
    #"Trimmed Text"

 But it still gave me the same error. I'm almost close to thinking that there's something wrong about how my sql and bi connection is set-up. Everything is working, except when I trim.

Thanks for the new approach. 

[MisterNL]

I had the same issue. I did a Clean in front of the Trim and it worked afterwards