taking a non-empty column value in case of empty values

[Anonymous]

Good day to all. I will try to summarize the essence briefly: I have a column with an average cost of one megabyte in the context of regions and settlements. But among them there are empty values (if there is no settlement). I Apend this querie with another one where the average values were calculated just for empty values. Thus, at the output, I have two columns with an average cost - 

"APPMb" and "N.APPMb". Now I need an action that will create a new column and will replace empty values of the APPMb column with non_empty values of N.APPMb values. Is it possible?

[v-xuding-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly mark the helpful answer as a solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from here.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

You could try the following code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQKhWJ1oJSMI28gQzDOG8AwNwDwTKM8UzDOF8IwhPDOQJgMIERsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"APP Mb" = _t, #"N.APP Mb" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"APP Mb", Int64.Type}, {"N.APP Mb", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [APP Mb] = null then [N.APP Mb] else [APP Mb])
in
    #"Added Custom"

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.