locked
Change filter PowerPivot Acces database RRS feed

  • Question

  • Hi Guys,

    I’m looking for some more info regarding filters that can be set on columns during the import in powerpivot through an Acces database.

    Currently I’m working with an Acces database that contains year results, I used powerpivot to import the data and set the year filter during the import wizard to 2015/2016 data.

    My question is can this filter be edited?

    I only want data from the current running year in combination with the previous closed year.

    For example:

    Current filter is set to 2015/2016

    When 2017 starts I want to adjust the filter to 2016/2017 (exclude 2015 data).

    Currently using office 2010 in combination with Microsoft PowerPivot SQL Server® 2012 SP1.

    Kind regards,

    Michael

    Friday, April 15, 2016 3:48 PM

Answers

  • The parameters of the import can be edited.

    I'll assume you are importing the data from Access directly from the Power Pivot interface since you didn't mention Power Query.

    When in the Power Pivot window, select the data table that is coming from Access if your model has more than one table. On the ribbon, go to the Design tab and select Table Properties.

    This should give you the Edit Table Properties window where you should be able to manually change the filters similar to using AutoFilters in regular Excel.

    If you wanted the parameter to be more dynamic so that it changed automatically next year, you could change the drop down in the top right corner from Table Preview to Query Editor and add a WHERE clause to the query that might be something along these lines:

    SELECT
       [TableName].*
    FROM [TableName]
    WHERE
       [Year] >= YEAR(NOW()) - 1
       

    This would give you all data with a year greater than or equal to the current year minus 1.

    You will obviously need to adjust the query based on your Access DB, Table Names, and Field Names, but this is the general pattern to try.

    Friday, April 15, 2016 5:00 PM
    Answerer

All replies

  • The parameters of the import can be edited.

    I'll assume you are importing the data from Access directly from the Power Pivot interface since you didn't mention Power Query.

    When in the Power Pivot window, select the data table that is coming from Access if your model has more than one table. On the ribbon, go to the Design tab and select Table Properties.

    This should give you the Edit Table Properties window where you should be able to manually change the filters similar to using AutoFilters in regular Excel.

    If you wanted the parameter to be more dynamic so that it changed automatically next year, you could change the drop down in the top right corner from Table Preview to Query Editor and add a WHERE clause to the query that might be something along these lines:

    SELECT
       [TableName].*
    FROM [TableName]
    WHERE
       [Year] >= YEAR(NOW()) - 1
       

    This would give you all data with a year greater than or equal to the current year minus 1.

    You will obviously need to adjust the query based on your Access DB, Table Names, and Field Names, but this is the general pattern to try.

    Friday, April 15, 2016 5:00 PM
    Answerer
  • Hi Mike, Many thanks! How have I missed this xD Kind regards, Michael
    Friday, April 15, 2016 6:42 PM