Forum Discussion
Solved
Based on duplicated value, replace valus in another column
| 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
3 Replies
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
may be I incert it wrongly, but it doesn't make any difference to my tables..
