Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter data in Advanced Editor

Hi all, Does anybody know a syntax for the advanced editor to filter data only for a certain years. Currently, the coming data from one query is from 2012 till 2020 and I would like to pull only i...
Need Help
Show and Tell
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi Anonymous ,

     

    You could filter date directly like below: 

     

     

    Or you could create a parameter to filter it. When you want to set other date range, you could only change the parameters.

     

    let
    Source = Excel.Workbook(File.Contents("C:\Users\xueding\Desktop\Financial Sample - Copy.xlsx"), null, true),
    financials3_Table = Source{[Item="financials3",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(financials3_Table,{{"Segment", type text}, {"Country", type text}, {"Product", type text}, {"Units Sold", type number}, {"Manufacturing Price", Int64.Type}, {"Sales", Int64.Type}, {"Date", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] > Date)
in
    #"Filtered Rows"

    Using Parameters in Power BI 

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

HI Anonymous ,

 

You can refer this video.

 

https://www.youtube.com/watch?v=qdhsq23cQhg

 

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous,

    Thank you for the video. I watched it and this option didn't work for my case, but I will try something similar or try to add 

    Table.SelectRows function.

    Best,
    • v-xuding-msft's avatar
      v-xuding-msft
      Community Support
      6 years ago

      Hi Anonymous ,

       

      You could filter date directly like below: 

       

       

      Or you could create a parameter to filter it. When you want to set other date range, you could only change the parameters.

       

      let
    Source = Excel.Workbook(File.Contents("C:\Users\xueding\Desktop\Financial Sample - Copy.xlsx"), null, true),
    financials3_Table = Source{[Item="financials3",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(financials3_Table,{{"Segment", type text}, {"Country", type text}, {"Product", type text}, {"Units Sold", type number}, {"Manufacturing Price", Int64.Type}, {"Sales", Int64.Type}, {"Date", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] > Date)
in
    #"Filtered Rows"

      Using Parameters in Power BI 

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi v-xuding-msft ,

        Thank you a lot. This way worked!

        Best,
        E.