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
Anonymous
Not applicable

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
1 ACCEPTED SOLUTION
erik_tarnvik
Solution Specialist
Solution Specialist

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. 

View solution in original post

4 REPLIES 4
jg1002
New Member

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.  

Anonymous
Not applicable

@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
Solution Specialist
Solution Specialist

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
Not applicable

Hi,

 

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

 

Best regards

Marius

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.