Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dashmarley11
Helper II
Helper II

Filtering Rows Based on Criteria

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_DateRateRateType
5/3/2024.054FTP
5/3/2024.055TLP
5/6/2024.051FTP
1 ACCEPTED SOLUTION
v-junyant-msft
Community Support
Community Support

Hi @dashmarley11 ,

Here is my sample data:

vjunyantmsft_1-1718160669501.png

 

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:

vjunyantmsft_2-1718160702192.png


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.

View solution in original post

4 REPLIES 4
v-junyant-msft
Community Support
Community Support

Hi @dashmarley11 ,

Here is my sample data:

vjunyantmsft_1-1718160669501.png

 

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:

vjunyantmsft_2-1718160702192.png


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
Solution Supplier
Solution Supplier

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

@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),

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. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.