Creating new data in a row depending on criteria comparing two tables

Hi mayyap,

To achieve your requirement, I think you can try Power Query. Please refer to following method:

Since you didn't share us your sample data. I have assumed it like below:

Table A and Master table:

1. Go to Edit Queries, choose Merge Queries and expand the columns:

2. Replace Table A.INTEREST value null to "Guarantor".

The entire M query is like:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyagkkzpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Loan number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Loan number", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Loan number"},#"Table A",{"Loan number"},"Table A",JoinKind.LeftOuter),
    #"Expanded Table A" = Table.ExpandTableColumn(#"Merged Queries", "Table A", {"Loan number", "Item", "INTEREST"}, {"Table A.Loan number", "Table A.Item", "Table A.INTEREST"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Table A",null,each if [Table A.Loan number] is null then "Guarantor" else [Table A.INTEREST],Replacer.ReplaceValue,{"Table A.INTEREST"})
in
    #"Replaced Value"

If above sample doesn't satisfy you. Please share us your table structure and some sample data which can help us make some tests. Also it will give us a right direction if you can share us the desired result based on your sample data.

Thanks,
Xi Jin.