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,

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.