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 information from 2018 until 2020. The current syntax is something like:

let
Source = 1Database("aa", "cbb", [TypedMeasureColumns=true]),
MAR1 = Source{[Id="MAR"]}[Data],
MAR2 = MAR1{[Id="MAR"]}[Data],
#"Added Items" = Cube.Transform(MAR2, {{Cube.AddAndExpandDimensionColumn, "[A]", {Cube.AddAndExpandDimensionColumn, "[C]", {Cube.AddMeasureColumn, "F"}})
in
#"Added Items"

Thank you.

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 

     

4 Replies

    • 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