Replacing Blanks in Power Query inside a Table.ReplaceValue() function

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 Zhi

If 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 Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.