Forum Discussion
Anonymous
2 years agoNot applicable
Top 5 upcoming dates
Hi. I have a dataset that consists of 12 columns with all sorts of dates 1 column with project id (not inc. in pic). I would like to look up the five date column that contains the date closest to t...
edhans
2 years agoCommunity Champion
You didn't really provide enough info. You gave no data, and you refrenced an image that doesn't exist. But you can use this code to keep only the most recent 5 dates for every project ID.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZRJjoQwDEXvwrqkJCZkOAti0fNw/wM0JUj5mXZLvYPg2M/f/qzr9DTdppSCzEGizOdLHS8tpPFY+dgREPVY8iOLBM0YQyq48Mi+Z+kse97Ybuv0fByljCRClo6qiyYXVpXu97EAAK2KkBhXi3K9XFH2a81lbJaxsFEBQHFU3RNVsjRe5oeuZK9HzfwPoXUaPSBeImPaHx0mgZIR1aDqOL9zvZ35kGJhOxnpxA2KgdszI54lKRiFxOCxX++OsgareJ1dELV98lbDmKlpVl6BbapyfVy222BFoDSsrjFbwriaNSHiOQ6N75f/QFKwz1+CRTM7cbNX68HoUlZDKe6m0EE9gOvr0AKbAInMeKm+jtHaJIknRbVQxmP09Sh35/o+YmeuiPE0CYS6cHn5IRVPMKGDjU9ZefS4bT8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, Date1 = _t, Date2 = _t, Date3 = _t, Date4 = _t, Date5 = _t, Date6 = _t, Date7 = _t, Date8 = _t, Date9 = _t, Date10 = _t, Date11 = _t, Date12 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date1", type date}, {"Date2", type date}, {"Date3", type date}, {"Date4", type date}, {"Date5", type date}, {"Date6", type date}, {"Date7", type date}, {"Date8", type date}, {"Date9", type date}, {"Date10", type date}, {"Date11", type date}, {"Date12", type date}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project ID"}, "Attribute", "Value"),
#"Grouped Rows" =
Table.Group(
#"Unpivoted Other Columns",
{"Project ID"},
{
{
"AllRows",
each
Table.FirstN(
Table.Sort(_, {{"Value", Order.Descending}}),
5),
type table [Project ID=nullable text, Attribute=text, Value=date]}
}
),
#"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Attribute", "Value"}, {"Attribute", "Value"})
in
#"Expanded AllRows"
//= Table.Sort(#"Unpivoted Other Columns",{{"Value", Order.Descending}})
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.