Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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!!!
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.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |