sorting by most current date range fiscal year and quarter

[bo_wang5]

I'm using a custom date table to convert my dates to Fiscal Year and Quarter, "YYYY-QQ" in power query. I'd like then to only filter this data to most current 10 quarters. since "YYYY-QQ" is not a date data type, how would I sort and tell power query that I'd like to see the 10 most current as new data is loaded in each quarter without manually filtering the most recent data.

[v-yingjl · Accepted solution]

Hi @bo_wang5 ,

You can try this query to filter lastest top10 value for each FY date:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XdJBDoMwDETRu7Cmwh5ISc6CuP81CF1U/rMLehrHGXFdi0K5nVvGsr5nfSLnaX7e6x8bTLCE7TQGD6B4YyvYfZ0vkFNPBmGdxuAAygsoOmxs1noyiupVUcvkH+8WNj6MG7mRTWtRmb5YrSrl2i1rew1j3qwg26OVZAsLZct+jIn3Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"FY Date" = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"FY Date", type text}, {"Value", Int64.Type}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type", {"FY Date"}, 
                {
                    {
                        "Data", each
                        Table.SelectRows( 
                            Table.AddIndexColumn(
                                Table.Sort(_,{"Date",Order.Descending}),"Index",1,1),
                                each [Index] <= 10
                        )
                            , type table [Date=nullable date, FY Date=nullable date, Value=nullable number, Index = nullable number]
                    }
                }
        ),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Date", "Value"}, {"Date", "Value"})
in
    #"Expanded Data"

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[serpiva64]

Hi,

You can try to add a column to your date table like this

if [Date]>DateTime.Date(( Date.AddMonths( DateTime.LocalNow(),-NrOfMonthes))) and [Date]<DateTime.Date(DateTime.LocalNow()) then 1 else 0

NrOfMonthes is a parameter 

 but i don't think it's so necessary 

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !