Getting most recent file based on reporting Month with different file names

[transform99]

I have 3 columns in Power BI with similar naming conventions to below. There are 2 distinct naming conventions:

- Weekly File MmmYY

- Washed File MmmYY

If a washed file exists for the month, I only want to return that file. If a washed file does not exist for that month, I want to return the most recent Weekly file available. How can this be done?

Name	Date Created	Report Month
Weekly File Nov23	1/11/2023	1/11/2023
Weekly File Nov23	8/11/2023	1/11/2023
Weekly File Nov23	15/11/2023	1/11/2023
Weekly File Nov23	22/11/2023	1/11/2023
Weekly File Nov23	29/11/2023	1/11/2023
Washed File Nov23	6/12/2023	1/11/2023
Weekly File Dec23	6/12/2023	1/12/2023
Weekly File Dec23	13/12/2023	1/12/2023
Weekly File Dec23	20/12/2023	1/12/2023
Weekly File Dec23	27/12/2023	1/12/2023
Weekly File Jan24	3/01/2024	1/01/2024
Washed File Dec23	3/01/2024	1/12/2023
Weekly File Jan24	10/01/2024	1/01/2024

In the above table the following would be correct files chosen:

november = Washed File Nov23 - 6/12/2023 - 01/11/2023.

december = Washed File Dec23 - 3/01/2024 - 1/12/2023.

January = Weekly File Jan24 -  10/01/2024 - 1/01/2024.

[dufoq3 · Accepted solution]

Hi @transform99,

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk9Nzc6pVHDLzElV8MsvMzJW0lEy1Dc01DcyQGPH6mBXbUGSakNTkpQbGZGm3BK38sTijNQUVOVm+oZGhA13SU3GptoIv2pDY5KUGxmQptycKOVeiXlGJkAVxvoGYM+ZgFXD2OjBAjMcVTUhsw0NsBseCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Date Created" = _t, #"Report Month" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date Created", type date}, {"Report Month", type date}}),
    Ad_NameHelper = Table.AddColumn(ChangedType, "NameHelper", each Text.BeforeDelimiter([Name], " "), type text),
    Ad_DateHelper = Table.AddColumn(Ad_NameHelper, "DateHelper", each Number.From(Date.ToText([Report Month], [Format="yyyyMM"])), Int64.Type),
    GroupedRows = Table.Group(Ad_DateHelper, {"DateHelper"}, {{"All", each
        [ a = Table.SelectRows(_, (x)=> x[NameHelper] = "Washed"),
          b = if Table.RowCount(a) > 0 then a else Table.FirstN(Table.Sort(_, {{"Date Created", Order.Descending}}), 1),
          c = Table.RemoveColumns(b, {"NameHelper", "DateHelper"})
        ][c], type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

[transform99]

Thanks @dufoq3 this works and is more elegant than the solution I was looking at implementing.

[dufoq3]

You're welcome. Enjoy.