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

[benjamin_sasin]

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

[dax · Accepted solution]

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.

[benjamin_sasin · Accepted solution]

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.

[durlav1993]

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

[Anonymous]

Easiest Solution: using no dax and no M code. In power query just press replace values. leave value to find as blank, value to replace with something you want to replace, then in advanced options tick match entire cells contents. Woalah!

[benjamin_sasin · Accepted solution]

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.

[dax · Accepted solution]

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.

[dax · Accepted solution]

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.

[Frank001]

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.

[elishaveriwa]

You really served my day 🤗 🤝.

[Anonymous]

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  

[Greg_Deckler]

Try using null instead of ""