REplacing value based on grouping in another columns and condition

[KM07]

Hello,

I've searched in forums and googled but I haven't found answer how to replace value if the condition is based on one column grouping and value of another column. If to look the sample data (there are other columns as well which are not basis of the replacing)

Currency	Node	Type
EUR	180d	OIS
EUR	180d	IB
EUR	270d	OIS
EUR	270d	IB
EUR	270d	TR
EUR	1yr	SW
GBP	7d	IB
USD	3d	IB
USD	3d	TR

What I need is that under each currency separately where Type "OIS" is reprsented everything else beside "TR" is changed to "OIS". If there's no "OIS", it should remain as it is. Result should be as follows:

Currency	Node	Type	Replace
EUR	180d	OIS	OIS
EUR	180d	IB	OIS
EUR	270d	OIS	OIS
EUR	270d	IB	OIS
EUR	270d	TR	TR
EUR	1yr	SW	OIS
GBP	7d	IB	IB
USD	3d	IB	IB
USD	3d	TR	TR

I can't use group by since I need the table to remain in the same format since there are other fields which I use in latter steps based on this new field.
Thanks in advance.

[KM07]

Great. I used it in my code from #"Changed Type" and it works. Thank you.

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg0NUtJRMrQwSAFS/p7BSrE6aIKeTkhiRuZYFEIFsSkMCUI2sLIISAaHg4XcnQKAHHMkjaHBLkCOMXYRkEGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Currency = _t, Node = _t, Type = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Currency", type text}, {"Node", type text}, {"Type", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Currency"}, {{"Rows", each _, type table [Currency=nullable text, Node=nullable text, Type=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "has OIS", each List.Contains([Rows][Type],"OIS")),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Added Custom", "Rows", {"Node", "Type"}, {"Node", "Type"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Rows", "Replace", each if [Type]="TR" or [has OIS] = false then [Type] else "OIS"),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Currency", "Node", "Type", "Replace"})
in
    #"Removed Other Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".