Forum Discussion
Replace blank value
Hi,
I was trying to replace blank value with some value by using replace value command in power query but the answer is same as blank value. Before that I have tried with trim column and after that I have used replace value in power query even that didn't work. also I have tried to replace it in the m query by querying it but it wont worked. Also tried different ways in desktop but not working can anyone help.
6 Replies
- Vijay_A_VermaMost Valuable Professional
Select that column - Transform menu - Format - Clean.
Now try to replace blank
- AnonymousNot applicable
It's not working.
- Vijay_A_VermaMost Valuable Professional
Can you share your pbix file without confidential data through Onedrive / any other file host like Google Drive, Drop Box, Box and share the link here?
- AlexisOlsonSuper User
There are different possible kinds of blank values. Here's an example that starts with a text column containing an empty string, null, whitespace, and "null" and shows what the replace value function should look like in each case.
let Source = Table.FromColumns({{"", null, " ", "null"}}, {"Text"}), #"Replaced Value" = Table.ReplaceValue(Source,"","EmptyString",Replacer.ReplaceValue,{"Text"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"ActualNull",Replacer.ReplaceValue,{"Text"}), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1"," ","Space",Replacer.ReplaceText,{"Text"}), #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","null","TextNull",Replacer.ReplaceText,{"Text"}) in #"Replaced Value3"Without seeing your specific table, I can't tell what sort of blank values you have. You can try adding a custom column to read the Unicode character(s) from your [TextColumn] like this and let me know what it returns:
Text.Combine( List.Transform( Text.ToList([TextColumn]), each Number.ToText(Character.ToNumber(_)) ), ", ")(An empty string will return an empty string, a null will throw an error, and any other character(s) should give a list of Unicode number(s).)
- AnonymousNot applicable
Tried with Concatenate and after that used the replace value in query editor. It was empty that's why I wasn't able to replace it with any value. So I have tried to Concatenate with "-" and then replaced that value.
- v-yanjiang-msftCommunity Support
Hi Anonymous ,
For empty, have you tried the below step which AlexisOlson mentioned, it seems works fine.
#"Replaced Value" = Table.ReplaceValue(Source,"","EmptyString",Replacer.ReplaceValue,{"Text"})Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.