Forum Discussion
Restrict data coming from denodo
HI, I am importing data from denodo which is having data from 2010 till 2020. I want to take just the data for last 3 years from today (if 2020 is latest then 2018,2019,2020 and if 2021 is latest...
Hi Anonymous ,
You could try below M code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TcxBDoAwCETRu7BuQhktylka7n8NEUvs9uXPzEnocrGwUCMhbwXgEYKSmzWT8cO3OQssQPcTYyQcC9CjGNvHC8gTK5GVKLk/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t, AMOUNT = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}}), Custom1 = Table.SelectRows(#"Changed Type", each [date]<=List.Max(#"Changed Type"[date]) and [date]> #date(Date.Year(List.Max(#"Changed Type"[date]))-2,1,1)) in Custom1Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Zoe,
Thanks for your quick help.
The solution you provided looks good but i want to do this filterization on data when i am importing data from denodo server which is in power query.
Not sure if this will work there
dax
Community Support
Community SupportHi Anonymous ,
You could try below M code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TcxBDoAwCETRu7BuQhktylka7n8NEUvs9uXPzEnocrGwUCMhbwXgEYKSmzWT8cO3OQssQPcTYyQcC9CjGNvHC8gTK5GVKLk/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t, AMOUNT = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}}),
Custom1 = Table.SelectRows(#"Changed Type", each [date]<=List.Max(#"Changed Type"[date]) and [date]> #date(Date.Year(List.Max(#"Changed Type"[date]))-2,1,1))
in
Custom1Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.