Forum Discussion

KM07's avatar
KM07
New Member
3 years ago
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 (there are other columns as well which are not basis of the replacing)

CurrencyNodeType
EUR180dOIS
EUR180dIB
EUR270dOIS
EUR270dIB
EUR270dTR
EUR1yrSW
GBP7dIB
USD3dIB
USD3dTR

 

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:

CurrencyNodeTypeReplace
EUR180dOISOIS
EUR180dIBOIS
EUR270dOISOIS
EUR270dIBOIS
EUR270dTRTR
EUR1yrSWOIS
GBP7dIBIB
USD3dIBIB
USD3dTRTR

 

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.

Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago
    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".

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago
    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".

  • KM07's avatar
    KM07
    New Member
    3 years ago

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