Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Powerquery filter only most recent data rows

Hi,

 

In a referenced table in PowerQuery I need to work only with latest data from the original table based on column YearMonth (format 202011, 202010, etc). So far we were manually copying newest data in separate file and imported that file as the new source.

 

Is it possible to filter data automatically based on the max value in the YearMonth column? For example now we are in November so I only need to work with rows that have 202011 value in them and that needs to dynamically change every time I refresh data for the following month. 

 

Thanks.

 

 

Need Help
  • Icey's avatar
    Icey
    5 years ago

    Hi Anonymous ,

     

    Try this:

     

    Method1:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA3NNQ3VNJRMlSK1UGIGAFFjJBEDMBqjFFEQGpMECKWYCWmyAIgFWZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.IsInCurrentMonth([Date])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"

     

    Method2:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA3NNQ3VNJRMlSK1UGIGAFFjJBEDMBqjFFEQGpMECKWYCWmyAIgFWZQAUOgvBFYiblSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each (Date.Year(DateTime.LocalNow())-Date.Year([Date]))*12 + Date.Month(DateTime.LocalNow()) - Date.Month([Date])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Custom] < Recent_N_Month),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"

     

    BTW, .pbix file attached.

     

     

    Best regards

    Icey

     

    If this post helps, then consider Accepting it as the solution to help other members find it faster.

3 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    Anonymous 

    In Power Query, Filter the Year Month Column by any value, for example, 202011. Replace this value with the following code. It will always filter to current year and month

     

    Date.Year(DateTime.FixedLocalNow()) * 100 + Date.Month(DateTime.FixedLocalNow())))  

    ________________________

    If my answer was helpful, please consider Accept it as the solution to help the other members find it

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube  LinkedIn

  • Icey's avatar
    Icey
    Community Support
    5 years ago

    Hi Anonymous ,

     

    Try this:

     

    Method1:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA3NNQ3VNJRMlSK1UGIGAFFjJBEDMBqjFFEQGpMECKWYCWmyAIgFWZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.IsInCurrentMonth([Date])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"

     

    Method2:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA3NNQ3VNJRMlSK1UGIGAFFjJBEDMBqjFFEQGpMECKWYCWmyAIgFWZQAUOgvBFYiblSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each (Date.Year(DateTime.LocalNow())-Date.Year([Date]))*12 + Date.Month(DateTime.LocalNow()) - Date.Month([Date])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Custom] < Recent_N_Month),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"

     

    BTW, .pbix file attached.

     

     

    Best regards

    Icey

     

    If this post helps, then consider Accepting it as the solution to help other members find it faster.