Latest Date records in direct query table

[prakash11440278]

Hi All,

I want latest date records in my table. How can i restrict the data using latest date in my table. I am using direct query connection which i want to do the changes in power query instaed of dax. Please help.

[v-easonf-msft · Accepted solution]

Hi, @prakash11440278 

If you just want to keep the latest date records in PowerQuery, you need apply date filter to your date column.

If you  just want to keep most recent record base on categort , please refer to below tutorial.

Keep most recent record on a table with Power Query 

Sample M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc5LCsAgDATQu2QtaKK0bvu9hHj/azS1ODRZJAw8hqQ1Ygq06XDkKElEo1APf6gTCmC3jcVBmVAHvOmwDU5OVog4wXkukHNIhnwPZI2X61QnuCMJctuOrt4f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Type = _t, Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Type", type text}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Id", "Type"}, {{"Data", each Table.Max(_,"Date")}}),
    #"Expanded Data" = Table.ExpandRecordColumn(#"Grouped Rows", "Data", {"Date", "Value"}, {"Date", "Value"})
in
    #"Expanded Data"

Best Regards,
Community Support Team _ Eason

[amitchandak]

@prakash11440278 ,

Measure =

Var _max = maxx(allselected(Table), Table[Date])

return

calculate(countrows(Table), filter (Table, Table[Date] =_max))

Use these type of measure in visual or use it as visual level filter

If the latest is based on the category

https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...