Based on duplicated value, replace valus in another column

[ShiMaria]

BATCH	PO
1	5
#	5
2	4
3	6

Hello, 

I'm looking for the solution how to replace the value (#) in one column (BATCH) with the another value (1) from the same column based on duplicated value (5) in another column (PO). 

Here is what i want to get: 

BATCH	PO
1	5
1	5
2	4
3	4

thank you in advance for any tip, 

Maria

[AlB]

Hi @ShiMaria 

Place this code in a blank query to see the steps. The #"Added Custom" step is the important one:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTJVitWJVlKGs4yALBMwyxjIMlOKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [BATCH = _t, PO = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"BATCH", type text}, {"PO", Int64.Type}}),
    
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [BATCH] = "#" then List.Max(Table.SelectRows(#"Changed Type", (inner)=> inner[PO]=[PO] and (inner)[BATCH]<>"#")[BATCH])  else [BATCH]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"BATCH"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "BATCH"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"BATCH", "PO"})
in
    #"Reordered Columns"

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[ShiMaria]

may be I incert it wrongly, but it doesn't make any difference to my tables.. 

[Anonymous · Accepted solution]

given that the AIB answer is correct, if you are better off with the PBI GUI, these are the steps you can follow to get the result:

order before column2 acsending, then column1 descending

finally, fill down column1