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 year then 2019,2020,2021)

 

 I see there is a a option in dropdown to  select  "is in the previous" but it selects data 2017,2018,2019 instead of 2018,2019,2020.

 

Is there any way to do it ?

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.

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      HI  Amit,

       

      WIll this work on a date column as i want my data to be resricted by date while imorting.

       

       

  • dax's avatar
    dax
    Community 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
        Community 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.