Forum Discussion
Getting most recent file based on reporting Month with different file names
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.
Hi Anonymous,
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
3 Replies
Hi Anonymous,
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