Forum Discussion
Replacing blank values with values using M Script
Hi Experts
How would you edit the following M Script so (when Host Name [Computer] has entries, but there are blanks. where there are blanks I want to copy the value from "Name" )
#"Replaced Value" = Table.ReplaceValue(#"Uppercased Text",each "Host Name [Computer]",each if "Host Name [Computer]" = "" then "Name" else "Host Name [Computer]",Replacer.ReplaceValue,{"Host Name [Computer]"}),
11 Replies
ImkeF , Can you help
Hi Anonymous ,
You can try this code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjTScTTSc87PVYrVAfKMdRyN9ZKhPB1HEwRbwdEUmWMG5ziamoIkobKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"A1,A1.com" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"A1,A1.com", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"A1,A1.com", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"A1,A1.com", Text.Clean, type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Cleaned Text"," ","",Replacer.ReplaceText,{"A1,A1.com"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each if Text.Range([#"A1,A1.com"], 0,1) = "," then
Text.Range([#"A1,A1.com"], 1,2) & [#"A1,A1.com"] else
[#"A1,A1.com"])
in
#"Added Custom"Ricardo
Hi Expert
Find attached sample data. I want the values in Column Names to populate into Column Host Name, when Host name is blank, using Mscript dynamically as the exel file is updated and also i still want to see all the rows in the table.
link:https://www.dropbox.com/s/50syhbx3d8zk1sq/Sample_.pbix?dl=0
Hi Anonymous ,
Try this code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZK9DoIwFEbfpTNNeqsDjLY0xligATQYw4JxcDDh/ScrxB+4ve3Yc04CX3q9sos/j/swjHB7jkKkLJmuDkYpB7pw/oq7pmZ9gtwMuxnhgkAuCMoF7ALlSuzKr+vZ6agc796HKwFcV7UJIkkj4LYtuKtzIozRuS1d+HtklM5tbs6RlqLA923xI1aRG/yh9QaLCv3lIozRwAaopWhgA9RSdLWB/qzTeLLT2ndWCkhFCv7dbiFgdtMkZVNZs7I3rO9f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Host Name" = _t, Name = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Host Name", type text}, {"Name", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Host Name] is null or [Host Name] = "" then [Name] else [Host Name])
in
#"Added Custom"Ricardo
