Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

FIlter Current week

Hi I have a table that I want filter away before loading in power bi, I have several week of data and just want to have current week.

I have only yearweek in my fact table, im using same table to several reports.
Thanks

Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

     

    You need to add a step name if you're putting it straight into Advanced Editor.

    It would look something like this:

    // This:

let
    Source = YourSource,
    SomeChanges = Function(Source, each DoStuff)
in
    SomeChanges

// ...would go to this:

let
    Source = YourSource,
    FilterRows = 
        Table.SelectRows(
            Source,
            each [YearWeek] = List.Max(Source[YearWeek])
        )
    SomeChanges = Function(FilterRows, each DoStuff)
in
    SomeChanges

     

    Pete

     

6 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    Hi Anonymous ,

     

    If your YearWeek column is an integer e.g. 202301, 202341 etc. then the following filter should work:

    Table.SelectRows(
    previousStepName,
    each [YearWeek] = List.Max(previousStepName[YearWeek])
)

     

    Pete

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi the code doesnt work

      • BA_Pete's avatar
        BA_Pete
        Super User
        3 years ago

         

        What doesn't work? Do you get an error? Do you not get the result you're expecting? What?

         

        Pete

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi got this when try to add it in Advanced editor
      Table.SelectRows( (Token '=' expected.)
      previousStepName,
      each [YearWeek] = List.Max(previousStepName[YearWeek])
      Regard

      • BA_Pete's avatar
        BA_Pete
        Super User
        3 years ago

         

        You need to add a step name if you're putting it straight into Advanced Editor.

        It would look something like this:

        // This:

let
    Source = YourSource,
    SomeChanges = Function(Source, each DoStuff)
in
    SomeChanges

// ...would go to this:

let
    Source = YourSource,
    FilterRows = 
        Table.SelectRows(
            Source,
            each [YearWeek] = List.Max(Source[YearWeek])
        )
    SomeChanges = Function(FilterRows, each DoStuff)
in
    SomeChanges

         

        Pete

         

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    3 years ago

    You can choose Transform data while pulling in the data into Power BI and in Transform Data (i..e. Power Query), you can reduce the amount of data before lading into Power BI. 

    Ideally the best way is to way put a SQL query into your Power BI import so that data pulled from database is also lesser.

    In first approach, full data will be imported but Power BI will be loaded with only required rows after you transform data.

    If you use SQL Query to pull in only required rows, then operation will be faster as only required rows are imported.