none
Power Query - Replace cells that have any number of blank spaces (i.e. " ", " ", " ", etc) with null value ("") RRS feed

  • Question

  • I am using power query to bring in a messy data table (from Excel) where some columns have cells that have text and some have a variable number of blank spaces (" ", "  ", "   ", etc) and some are null (""). I cannot seem to get the "transform" action to work as I think it should... that is, if the cells has one or more blank spaces (and only blank spaces) in it, make the cell null (nothing in it). How would I write the transform formula?

    Using the transform wizard in Power Query to add a parsing step, the best I can do is this: 

    = Table.ReplaceValue(#"Replaced Value","   ","",Replacer.ReplaceValue,{"EXT_DESC", "ATTIC_DESC"})

    Shouldn't I be able to get something with "Text.trim" into the Applied Steps sequence? How?

    Friday, December 11, 2015 11:53 PM

Answers

  • You trim first and then replace:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column", type text}}),
        #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{},Text.Trim),
        #"Replaced Value" = Table.ReplaceValue(#"Trimmed Text","",null,Replacer.ReplaceValue,{"Spalte"})
    in
        #"Replaced Value"


    Imke Feldmann TheBIccountant.com

    Saturday, December 12, 2015 7:52 PM
    Moderator

All replies

  • You trim first and then replace:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column", type text}}),
        #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{},Text.Trim),
        #"Replaced Value" = Table.ReplaceValue(#"Trimmed Text","",null,Replacer.ReplaceValue,{"Spalte"})
    in
        #"Replaced Value"


    Imke Feldmann TheBIccountant.com

    Saturday, December 12, 2015 7:52 PM
    Moderator
  • Hi Imke,

    I know this is an older post, but I have a similar issue. I have column headers with leading and/or trailing spaces in supplied files. Being supplied, I don't have control over them, but I would like to use Text.Trim to trim the spaces from the column headers but nothing else. Is there a way to do that?

    Thanks very much

    Wednesday, March 27, 2019 9:23 PM
  • Yes, you can do it like so:

    Table.RenameColumns(List.Zip({Table.ColumnNames(Source), 
                                  List.Transform(Table.ColumnNames(Source), 
                                                 Text.Trim)}
                                                )
                                   )


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Wednesday, March 27, 2019 9:40 PM
    Moderator
  • Thanks for your quick response.

    = Table.RenameColumns(List.Zip({Table.ColumnNames(#"Promoted Headers"),List.Transform(Table.ColumnNames(#"Promoted Headers"),Text.Trim)}))

    This returns an expression error:

    Expression.Error: 1 arguments were passed to function which expects between 2 and 3.
    Details:
        Pattern=
        Arguments=List

    Thursday, March 28, 2019 1:54 PM
  • I found an easier way to do this:

    = Table.TransformColumnNames(#"Promoted Headers", (columnName as text) as text => Text.Trim(columnName))

    Tuesday, April 2, 2019 6:09 PM