Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Replace BLANK with text

Hi,

First of all, thanks for amazing help on the forum! Always a lot of good help.

So, what I hope to get some help with now:

I have some BLANK in my data model i want to replace with a text string. I want to do this changes with power query. When I mark the column, press replace I dont know what BLANK should be written as.

I have tried: BLANK, nothing, space, 0, «», « » but this does not work.
Earlier I had some NULL cells and this was easy to replace. I used NULL and then replaced it with text.

Anyone knows how I can replace BLANK?

Best regards
Marius
Need Help
  • erik_tarnvik's avatar
    erik_tarnvik
    8 years ago

    Assuming that with BLANK, you mean that the cells are empty on the screen: you may have some invisible characters in those fields, such as a space. You can find that out by using Transform-Extract-Length in Power Query, which will replace the content of the cells in your column with the length of whatever is in those cells. If it is anything other than 0, the cells aren't actually empty, they just look that way.

     

    If the cells are not actually empty, you need to figure out what's in them.

     

    If what you see on the screen is the text "BLANK", use the same method, the length should come out as 5. Anything else, same problem, you have some invisible characters in that text. 

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    @all you can use below formula to replace a blank with a text.

    Newcolumn= if(LEN('Table'[columnvlue])=0 ,"Not available",'Table'[columnvlue])

    if it's help don't forget to give a thanks 🙂

  • erik_tarnvik's avatar
    erik_tarnvik
    Solution Specialist
    8 years ago

    Assuming that with BLANK, you mean that the cells are empty on the screen: you may have some invisible characters in those fields, such as a space. You can find that out by using Transform-Extract-Length in Power Query, which will replace the content of the cells in your column with the length of whatever is in those cells. If it is anything other than 0, the cells aren't actually empty, they just look that way.

     

    If the cells are not actually empty, you need to figure out what's in them.

     

    If what you see on the screen is the text "BLANK", use the same method, the length should come out as 5. Anything else, same problem, you have some invisible characters in that text. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi,

       

      this was a little embarrassing, but thank you very much for your help. It worked excellent.

       

      Best regards

      Marius

  • jg1002's avatar
    jg1002
    New Member
    3 years ago

    1. Right click on the column

    2. Choose "Replace Values"

    3. Leave "Value To Find" empty (do not type in the box)

    4. Enter in the desired text in the "Replace With" cell

    5. Click ok

     

    This is for truly empty cells to replace with text.