How to remove #NA globally across all tables

[acerNZ]

Hi Experts,

Is there a way to remove #NA by replace with "null" across all the tables instead of clicking on each coloumn to replace it

Thanks in advance

[acerNZ]

Hi @Jimmy801 Do you agree, it is too easy to just select entire excel sheet and use find and replace than this.

[Jimmy801]

Hello @acerNZ 

I cannot completely follow you. For sure, if you can change your datasource it's better. But in Power Query you need do do like this. But Power Query is mashup engine and not Excel. The same would it be how to write a VBA to do this 🙂

BR

Jimmy

[acerNZ]

Thank you @Jimmy801 , As my table has more than 20 columns, I will give it a try tomorrow. Thanks a lot

[Jimmy801]

Hello @acerNZ 

use Table.ReplaceValue and in the last paramater use the function Table.ColumnNames and refer to your prior step. Here an example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUSpOTEkDUVAyMU0pVidayQnIUfZzRCFB4hBlELFEBCc2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    ReplaceAllNV = Table.ReplaceValue
    (
        #"Changed Type",
        "#NA", null, Replacer.ReplaceValue, Table.ColumnNames(#"Changed Type")
    )
in
    ReplaceAllNV

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[acerNZ]

Thank you @Jimmy801  I have about 20 coloumns with different names, do I have to change the coloumn names and add the columns accordingly in my work data. Thank you so much

[Jimmy801 · Accepted solution]

Hello @acerNZ 

at the end you always want to replace #NV with null, right?

Then the trick is to use Table.ColumnNames in the last parameter of Table.ReplaceValue to dynamically use all column names of your table within Table.ReplaceValue

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy