Forum Discussion
mshilendhari
6 years agoFrequent Visitor
Date since last added in Power Query or DAX
Hi all, I have a scenario where I need to show the date when a part became watchlist. Data is in the format shown below and this is filtered for one part. In this example, this part was initially...
edhans
Community Champion
6 years agomshilendhari - see if this is what you want. If not, please provide data per links below and how the calculation should be arrived at. The below M code turns this:
into this. You can see the items in red retain the latest Watchlist date for the same item.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUbLUN9Q3MjAyADLDE0uSM3Iyi0uUYnVgkkb4JI1hkp55xQWpySWZ+XlIsibYtToBRSxwWQqRxGEpRNIYn6QJDhdBZE2xycYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Date = _t, Status = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Grouped Rows" =
Table.Group(
#"Changed Type",
{"Item"},
{
{
"WatchListDate",
each
let
varStatus = "Watchlist",
varGroupTable = _
in
Table.AddColumn(
_,
"Test",
each
let
varCurrentDate = [Date]
in
List.Max(
Table.SelectRows(
varGroupTable,
each [Status] = varStatus and [Date] <= varCurrentDate
)[Date]
)
)
}
}
),
#"Expanded WatchListDate" = Table.ExpandTableColumn(#"Grouped Rows", "WatchListDate", {"Date", "Status", "Test"}, {"Date", "Status", "Test"})
in
#"Expanded WatchListDate"
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.