Forum Discussion
Replacing Blanks in Power Query inside a Table.ReplaceValue() function
I am trying to figure out how to replace blanks in Power Query, but all Google an Forum search lead me to using DAX, which is not what I am looking for, as I want to use Power M query. Also, searching for "blank" in the Power M Query documentation leads to nothing relevant.
I tried replacing "" with text, assuming "" is blank, but that somehow replaces the majority of rows and yet still leaves a couple of rows as "(blank)".
I'm using a Table.ReplaceValue() function. How can I replace blanks within that?
Thanks
Hi benjamin_sasin ,
You said that some rows didn't replace blank, right? I think this might be cause by space, some rows are " " instead of "", so when you use "", it doesn't work. You could try below M code to see the difference and see whetehr it work or not
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTJUitWJVkoGssAMBSDDGMwCMvJKc3Lg0qZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"Column1", Int64.Type}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "name", "name - Copy"), #"Replaced Value1" = Table.ReplaceValue(#"Duplicated Column","","replace",Replacer.ReplaceValue,{"name - Copy"}), #"Replaced Value" = Table.ReplaceValue(#"Replaced Value1",each [name],each if Text.Trim([name])="" then "replace blank" else [name],Replacer.ReplaceValue,{"name"}) in #"Replaced Value"Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks everyone.
So it turns out the problem wasn't Power Query, but on Power Bi.
My Table B with blanks was connected with another Table A, where Table A Left Join Table B. As a result, some rows in A didn't have a matching row in B and so the output was "blank", so I had to created a calculated column on Power BI to correct the gap.
Hi benjamin_sasin ,
When create relationship, records don't match will cause blank row, you could try to create measure or column, then apply this in filter and set "is not blank" to see whether it work or not. Or try to set many-to many to see whether it work or not.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 Replies
Hi benjamin_sasin ,
You said that some rows didn't replace blank, right? I think this might be cause by space, some rows are " " instead of "", so when you use "", it doesn't work. You could try below M code to see the difference and see whetehr it work or not
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTJUitWJVkoGssAMBSDDGMwCMvJKc3Lg0qZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"Column1", Int64.Type}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "name", "name - Copy"), #"Replaced Value1" = Table.ReplaceValue(#"Duplicated Column","","replace",Replacer.ReplaceValue,{"name - Copy"}), #"Replaced Value" = Table.ReplaceValue(#"Replaced Value1",each [name],each if Text.Trim([name])="" then "replace blank" else [name],Replacer.ReplaceValue,{"name"}) in #"Replaced Value"Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You really served my day 🤗 🤝.
Thank you so much! Your solution perfectly addressed my issue.
I've noticed that certain rows contain whitespace characters (" ") instead of true empty strings ("").
However, when attempting to apply the DAX TRIM() function within table view columns, it fails to clean these values, whereas the same operation works flawlessly in Power Query's M editor. I'm still unclear about this behavioral discrepancy between the two environments.
- Try using null instead of ""
Hi benjamin_sasin ,
did you try something like this?
Table.ReplaceValue(#"Added Custom","(blank)",null,Replacer.ReplaceValue,{"File Date", "Expiry Date", "2011"})I.e. searching for "(blank)" rather then ""...
Kind regards,
JB
Thanks everyone.
So it turns out the problem wasn't Power Query, but on Power Bi.
My Table B with blanks was connected with another Table A, where Table A Left Join Table B. As a result, some rows in A didn't have a matching row in B and so the output was "blank", so I had to created a calculated column on Power BI to correct the gap.
Hi benjamin_sasin ,
When create relationship, records don't match will cause blank row, you could try to create measure or column, then apply this in filter and set "is not blank" to see whether it work or not. Or try to set many-to many to see whether it work or not.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
i have to separate excel sheet 1. headcount(Col-DOJ) 2. attrition (col-LWD)
so , i have to find out the current emp count abstract from the attrition emp.
i used following DAX :
_noofHeadsCurr =
CALCULATE(
DISTINCTCOUNT(headcount[Employee ID]),
CROSSFILTER('calendar'[date], headcount[DOJ], None),
headcount[LWD] = BLANK()
) +0
