Forum Discussion
Solved
REplacing value based on grouping in another columns and condition
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 (t...
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".
Great. I used it in my code from #"Changed Type" and it works. Thank you.