Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
Tutorial Requests
  • dax's avatar
    dax
    6 years ago

    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
    Custom1

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

dax's avatar
dax
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous , 

You could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    dax
    Icon for Community Support rankCommunity Support
    6 years ago

    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
    Custom1

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.