Replacing blank values with values using M Script

[Anonymous]

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]"}),

So as an example data is:

Host Name {Computer], Name

A1,A1.com

A2,A2.Com

A3,A3.com

,A4.com

, A5.com

, A6.com

A55, A55.com

To read

Host Name {Computer], Name

A1,A1.com

A2,A2.Com

A3,A3.com

A4.com,A4.com

A5.com, A5.com

A6.com, A6.com

A55, A55.com

[camargos88]

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

[Anonymous]

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

[camargos88]

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

[Anonymous]

hi Expert

i do not want to add an addtional column but add the values from Name column into Host name coumn where we have blanks, if host name column has a value then ignore.

[camargos88]

Hi @Anonymous ,

You can use this script and drop the others 2 columns. 😃

Ricardo

[Anonymous]

Apologies not sure i follow can you kinldy add to the demo file. and re upload please. with the other two columns droppped.

[camargos88 · Accepted solution]

Hi @Anonymous ,

Try this file PBIX 

Ricardo

[Anonymous]

Apologies i cannot access one driver from this machine. can you add PBIX so i can down load please.

[camargos88]

Try @Anonymous ,

Could you download it ?

[Anonymous]

Yes thank you very much.

[amitchandak]

@ImkeF , Can you help