Forum Discussion

gmasta1129's avatar
gmasta1129
Resolver I
1 year ago

Find Text in a column based off portfolio code

Hello,   I am looking to create a formula where it finds any text in a row and pulls the text for each row that contains the same portfolio code.    Example #1: portfolio code 545454 has 3 rows o...
Need Help
Omid_Motamedise's avatar
Omid_Motamedise
Super User
1 year ago

Hi gmasta1129 

Just copy the below code and past it into the advance editor

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjUBwUMLlHSUfB2D3D39lGJ1UESx8s3MjYBoQPjBAUGuji6kmxILAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Portfolio Code" = _t, #"Interest Rate Code" = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Portfolio Code"}, {{"Count", each _, type table [Portfolio Code=nullable text, Interest Rate Code=nullable text]}, {"NewColumn", each List.RemoveItems(_[Interest Rate Code],{null,""}){0}}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Portfolio Code"}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Removed Columns", "Count", {"Portfolio Code", "Interest Rate Code"}, {"Portfolio Code", "Interest Rate Code"})
in
    #"Expanded Count"