How to filter conditional If in power query

Question

Hi i have a column in power query where the data is my sheet name and the names are ABC 2023 and ABC 2023 B2. I want to filter it such that if my source does not have a ABC 2023 B2, it will filter and return only ABC 2023. But if there is a ABC 2023 B2 worksheet then it will give me the one with B2 instead.&nbsp;&nbsp;i tried using text.contains but my formula is not workingHere's a snapshot of what i meant&nbsp;Any help is appreciated!

anonymous · Accepted Answer

Hi&nbsp;flow12345&nbsp;,My idea is to filter for rows ending in "B2", otherwise filter for shortest character lengths:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVjAyMDJWitVBcBScDJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Custom.Name = _t]),
    Custom1 = if Table.RowCount(Table.SelectRows(Source, each Text.EndsWith([Custom.Name], "B2"))) &lt; 1 then Table.SelectRows(Source, each Text.Length([Custom.Name]) =  List.Min(List.Transform(Source[Custom.Name],each Text.Length(_))) ) else Table.SelectRows(Source, each Text.EndsWith([Custom.Name], "B2"))
in
    Custom1
Best Regards,GaoCommunity Support Team
&nbsp;
If there is any post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;&nbsp;to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know.&nbsp;Thanks a lot!
How to get your questions answered quickly&nbsp;--&nbsp;&nbsp;How to provide sample data in the Power BI Forum

Forum Discussion

Related content

Power Query conditional filtering

Conditional Filtering

Grouped summaries with conditional filtering

Advanced filtering in power query - multiple conditions

Filter with conditions