Powerquery filter only most recent data rows

[Anonymous]

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.

[Icey · Accepted solution]

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.

[Ashish_Mathur]

Hi,

If you have a new file for each month, then follow the technique shown in the video here - https://www.youtube.com/watch?v=yL11ugShdrk.

Hope this helps.

[Fowmy]

@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