How changing the some of the values of a column (null) to the other values of the column condition

[danielvidal]

Hi,

I have 2 columns, first one, same value ( PO #) the other one 2 values (null) and a SO #. How can I change the value of null to be the same as the SO..considering there may be hundreds of POs and hundreds of scenarios in which there's a null value

PCPV

PC210639	NO PV
PC210639	NO PV
PC210639	NO PV
PC210639	NO PV
PC210639	PV210560
PC210639	PV210560
PC210639	PV210560
PC210639	PV210560
PC210639	PV210560
PC210639	PV210560
PC210639	PV210560
PC210639	PV210560
PC210639	PV210560
PC210639	PV210560
PC210639	PV210560
PC210639	PV210560
PC210639	PV210560
PC210639	NO PV
PC210639	NO PV
PC210639	NO PV
PC210639	NO PV
PC210639	PV210560
PC210639	PV210560
PC210639	PV210560
PC210639	PV210560
PC210639	PV210560
PC210639	PV210560
PC210639	PV210560
PC210639	PV210560
PC210639	PV210560
PC210639	PV210560
PC210639	PV210560
PC210639	PV210560
PC210639	PV210560

I have tried tones of things but I don't know how to tell power query to do this automatically for all cases

Cheers

[v-yalanwu-msft · Accepted solution]

Hi, @danielvidal ;

If i understand right , you could add a conditional column.

Then delete orginal column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCnA2MjQwM7ZU0lHy81cICFOK1aGmYEAYkGlqZjAqTpr4aFwMsLgCSigqYJNGCMQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PO #" = _t, #"SO #" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PO #", type text}, {"SO #", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [#"PO #"] = " " then [#"SO #"] else [#"PO #"]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"PO #"})
in
    #"Removed Columns"

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.