Filtering Rows Based on Criteria

[dashmarley11]

I have a Power Query that I need to filter based off of specific criteria.  The query pulls in daily rates for FTP and TLP, but sometimes only the FTP rates come in and I want to filter the results by the MAX Effective date but ONLY when both rate types come through.  In the example below, I wouldn't want to only filter by the Max Effective (5/6/24) date because it would only filter in FTP rates, so I would want the results to filter to 5/3/2024 because both RateTypes are present in the 5/3/24 data.

Effective_Date	Rate	RateType
5/3/2024	.054	FTP
5/3/2024	.055	TLP
5/6/2024	.051	FTP

[v-junyant-msft · Accepted solution]

Hi @dashmarley11 ,

Here is my sample data:

And put all of this M function into the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtU30jcyMDJR0lHSMzA1AFJuIQFKsTroMkZAKsQHJmOMJGOCogdZxhRFjxmSjCGKHnMkGXMUPcgyFjCZWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Effective_Date = _t, Rate = _t, RateType = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Effective_Date", type date}, {"Rate", type number}, {"RateType", type text}}),
    ftpRows = Table.SelectRows(#"Changed Type", each [RateType] = "FTP"),
    tlpRows = Table.SelectRows(#"Changed Type", each [RateType] = "TLP"),
    commonDates = Table.SelectRows(ftpRows, each List.ContainsAny(tlpRows[Effective_Date], {[Effective_Date]})),
    maxDate = List.Max(commonDates[Effective_Date]),
    finalRows = Table.SelectRows(#"Changed Type", each [Effective_Date] = maxDate and ([RateType] = "FTP" or [RateType] = "TLP"))
in
  finalRows

And the final output is as below:

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

[ExcelMonke]

You can consider something along the lines of:

MEASURE =
IF (
    CALCULATE (
        COUNT ( DataTable[RateType] ),
        ALLEXCEPT ( DataTable, DataTable[Effective_Date] )
    ) = 2,
    MAX ( DataTable[Effective_Date] ),
    ""
)

What this measure does is count the RateTypes at each date, and if there are two types (indicating both FTP and TLP are present), then it will return the MAX Date

[dashmarley11]

@ExcelMonke  Rather than a Custom Measure, is there a way to implement the same logic in the Advanced Editor query?  Below is the code that i'm currently using to filter the data by the Max Effective Date, but I need to include the logic that would filter in the most recent data ONLY when both RateTypes are available for a given date.

let
Source = Table.Combine({TLPRates, FTPRates}),
#"MaxDate" = Table.SelectRows(#"Source", let latest = List.Max(#"Source"[EffectiveDate]) in each [EffectiveDate] = latest),

[ExcelMonke]

Unfortunately I am not too fluent in Power Query M Language to help with it in the Advanced Editor query. Whilst Advanced Editor is very powerful and can help with performance, however, if this can be done as a measure, I would recommend going that way.