Find Text in a column based off portfolio code

[gmasta1129]

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 of data.  IN column "Interest Rate Code" 2 rows are blank but the 1st row contains the word "MARGIN".  

The new column should find the text (in this case its "MARGIN") and pull that word for each row that contains this portfolio code.  

Example #2: portfolio code 672672,  3 rows are blank and 1 row contains the word "SPREAD".  In my new column, the word "SPREAD" should pull into each row that contains this portfolio code. 

Example #3: portfolio code 123123 is contains no data in column "Interest Rate Code" for all 3 rows and should show as blank in the new column

Column #1                  Column #2             ** New Column

Portfolio Code      Interest Rate Code        New Column

545454                         MARGIN                      MARGIN

545454                                                             MARGIN

545454                                                             MARGIN 

672672                                                             SPREAD

672672                                                             SPREAD

672672                        SPREAD                         SPREAD

672672                                                             SPREAD

123123

123123

123123

[Omid_Motamedise]

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"

[gmasta1129]

@jgeddes , please note, column #2 is a calculated column.  My table has 30 columns and 14,300 rows. The calculated columns are not in the actual table.  A formula would work best in this scenario.  

[v-cgao-msft]

Hi @gmasta1129 ,

Calculated columns are located downstream of Powerquery; they are not visible in Powerquery. So what is the logic of the calculated column? If you want to use it in Powerquery, you need to rewrite it in Powerquery.

Also please try this calculated column to see if it solves the problem.

Column = CALCULATE(MAX('Table'[Interest Rate Code]),ALLEXCEPT('Table','Table'[Portfolio Code]))

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[jgeddes]

You can use the Group By feature to do this. 
Select the 'PortfolioCode' column and click on the Group By icon.

Choose to aggregate by all rows and aggregate by the max of 'InterestRateCode'.

You should end up with something like this...

Expand the '_nested' column choosing only the 'InterestRateCode' and deselect 'use original column...'

You should end up with your desired result...

Hope this gets you pointed in the right direction.