cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
benjamin_sasin
Resolver I
Resolver I

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

3 ACCEPTED SOLUTIONS
dax
Community Support
Community Support

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.

View solution in original post

benjamin_sasin
Resolver I
Resolver I

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.

View solution in original post

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.

View solution in original post

5 REPLIES 5
benjamin_sasin
Resolver I
Resolver I

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.

View solution in original post

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.

View solution in original post

dax
Community Support
Community Support

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.

View solution in original post

jborro
Solution Sage
Solution Sage

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
Super User IV
Super User IV

Try using null instead of ""

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Kudoed Authors