Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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
Thanks @dufoq3 this works and is more elegant than the solution I was looking at implementing.