Filter Rows based on validity date within group

[SevanT]

Hi all,

I have a bit of a problem that I cannot fix by myself. So hopefully this forum can help me with that.

My goal ist to create a monthly database with loan contracts. These loan contracts have securities which are maintained in a differen

td data table. This data tables looks as such:

Contract ID	Type of Security	Location of Security	valid until
1	Check	Safe	1.1.2022
1	Check	Safe	1.1.2025
1	Mortgage		1.1.2026
2	Guarantor	Copy	1.1.2020
2	Check	Safe	1.1.2022
3	Guarantor	Copy	1.1.2020
3	Guarantor	Copy	1.1.2030

My contract table contains one Contract ID per row with several financial and contractual fields. What I would like to do now is map the corresponding security to the contract table BUT ONLY the security that is valid at the reporting date.

For example:

I am creating a report for Nov 2024. The corresponding securities should be valid at reporting date, i.e. at Nov. 31st 2024, or the latest security available for the contract ID. 

Contract ID	Start	End	Amount	Type of Security	Location of Security
1	...	...	...	Check	Safe
2	...	...	...	Check	Safe
3	...	...	...	Guarantor	Copy

How would I go about this? 
Does it make sense to group and shorten the security data table to just single contract ID at the given report date and then map it to the contract table?

Thank you very much for your help!

[jgeddes · Accepted solution]

You can filter your security table to dates greater than or equal to the report date as you mentioned and then merge it into the contract table using the 'Contract ID' and 'Type of Security' as the key in both tables. 

Note that this method will not return any values for contract 2 as the security is only valid to 2022 in your example data.

[Omid_Motamedise]

Initiilly filter the td data table for the validity after november then use merge to combine the tables

[Anonymous · Accepted solution]

Hi @SevanT ,
Thanks for jgeddes reply.

You can try the following code to filter safe table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLOSE3OBtLBiWmpQMpQz1DPyMDISClWB5+8KVzeN7+oJD0xHSSngJA3A8sbAQXcSxOLEvNK8otAZuUXVCLUGMDV4HODMRFm4FVjDFQTCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Contract ID" = _t, #"Type of Security" = _t, #"Location of Security" = _t, #"valid until" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Contract ID", Int64.Type}, {"Type of Security", type text}, {"Location of Security", type text}, {"valid until", type date}}),
    FilteredRows = Table.SelectRows(#"Changed Type", each [valid until] > #date(2024, 11, 30)),
    FilteredCheckSafe = Table.SelectRows(FilteredRows, each [Type of Security] = "Check" and [Location of Security] = "Safe"),
    NonFilteredRows = Table.SelectRows(#"Changed Type", each [valid until] <= #date(2024, 11, 30)),
    GroupedRows = Table.Group(NonFilteredRows, {"Contract ID"}, {{"MaxDateRow", each Table.Max(_, "valid until")}}),
    ExpandedRows = Table.ExpandRecordColumn(GroupedRows, "MaxDateRow", { "Type of Security", "Location of Security", "valid until"}),
    FilteredCheckSafeIDs = Table.SelectColumns(FilteredCheckSafe, {"Contract ID"}),
    FilteredExpandedRows = Table.SelectRows(ExpandedRows, each not List.Contains(FilteredCheckSafeIDs[Contract ID], [Contract ID])),
    Result = Table.Combine({FilteredCheckSafe, FilteredExpandedRows})
in
    Result

Then merge the security table with the contract table based on the Contract ID

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jgeddes · Accepted solution]

You can filter your security table to dates greater than or equal to the report date as you mentioned and then merge it into the contract table using the 'Contract ID' and 'Type of Security' as the key in both tables. 

Note that this method will not return any values for contract 2 as the security is only valid to 2022 in your example data.