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

[mayyap]

Hi,

I have a problem which I have been mulling over for ages, so hopefully someone here can help me solve the issue.

I have Table A with multiple fields (too many to list), and another master table with just one column containing unique loan numbers. I want to compare Table A and this master table, and say if the loan number in the master table is not found in Table A, then create a new row in Table A with the field "LOAN" with the loan number, field "INTEREST" with "Guarantor", and the other fields null.

[mayyap]

Sorry for not replying earlier, and also for not posting sample data. The main reason was because I was thinking of ways to explain my situation without going too deep into detail. But I have come up with sample data to better illustrate.

The situation is this:

A loan has several interested parties (borrower, investor, guarantor etc.) Each interested party can play several roles at the same time, i.e. interested party A can be a borrower and a guarantor at the same time. Each interested party can also be tied to several loans, and play different roles in each loan.

I have a table that lists out each loan's interested party and the respective role/interest they play in that loan. Let's call this table "INTEREST".

Here is the loan number master table:

 

As you can see from the first table, loan 1002 doesn't have a guarantor. I want to compare both tables and say that if the loan doesn't have a guarantor, please add a row in table "INTEREST" as such:

I would like to be able to do this for multiple loans at the same time, and without having to duplicate the "INTEREST" table and filtering out the non-guarantor parties.

Thanks in advance!!!

[v-xjiin-msft · Accepted solution]

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.

[Greg_Deckler]

@Vvelardebailed me out on a similar kind of thing although I guess it depends on whether you want to do this in M or DAX but I would think that the potential technique would be the same.

https://community.powerbi.com/t5/Desktop/quot-Add-quot-a-missing-row/m-p/364379#M164689