Forum Discussion

lgo's avatar
lgo
Frequent Visitor
2 years ago
Solved

How to filter by last value provided within a month

Hello!    My data is provided weekly but i would like to get it monthly. The rule should be: latest value provided within a month = monthly value. Is that possible?   My data looks like this, see...
Need Help
  • slorin's avatar
    slorin
    2 years ago

    Hi,

     

    Maybe with Table.Group

     

    = Table.FromRecords(
        Table.Group(
           Prev_Step, 
           {"Date"},
           {{"Data", each Table.Max(_,"Date"), type record}},
           GroupKind.Local,
           (x,y) => Byte.From(Date.StartOfMonth(x[Date])<>Date.StartOfMonth(y[Date]))
         )[Data]
       )

    Stéphane 

dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
2 years ago

There are many ways:

Edit 2nd step YourSource = Source (refer to your data)

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDXNzDUNzIwMlaK1YlWMjRB4RoZonItULgGQMVGSHoNUbkWKFwjUwQ3FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    YourSource = Source,
    Ad_YearMonth = Table.AddColumn(YourSource, "Year Month", each Number.From(Date.ToText(Date.From([Date], "sk-SK"), "yyyyMM")), Int64.Type),
    #"Grouped Rows" = Table.Group(Ad_YearMonth, {"Year Month"}, {{"Date", each List.Max([Date]), type nullable date}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Date"})
in
    #"Removed Other Columns"