Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
Hi @Jimmy801 Do you agree, it is too easy to just select entire excel sheet and use find and replace than this.
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
Thank you @Jimmy801 , As my table has more than 20 columns, I will give it a try tomorrow. Thanks a lot
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
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
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