1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pass a variable onto Power Query

Discussion in 'Power Pivot, Power Map etc' started by aggie81, May 18, 2017.

  1. aggie81

    aggie81 Member

    Messages:
    51
    Is it possible to use a formula to limit the years of information that a Power Query pulls into a workbook from a SQL database?

    something like: year field is >=YEAR(TODAY())-5

    So that the query for year 2017 drops off 2011 pulling in only information from years 2012 to 2017, in the year 2018 drops 2012 and so on.

    Thanks,
    Lee
  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    14,669
  3. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,225
    Yes it's possible. There are many ways to do it.

    Simplest solution is to use WHERE clause in your SQL statement.

    Assuming that there is [YEAR] column in source SQL table.

    Code (vb):
    Select *
    From dbname.dbo.Table1 as t1
    Where t1.YEAR >=  Datepart(yyyy, Getdate()) - 5
    Alternately you can set up named range and pass it on to PowerQuery by editing Query in Advanced Editor. You can see how single cell named range is used to pass on variable to PowerQuery in thread below.

    http://chandoo.org/forum/threads/complex-data-rearrange.30459/#post-182012
    aggie81 and NARAYANK991 like this.
  4. aggie81

    aggie81 Member

    Messages:
    51
    Thanks, I will try the SQL WHERE and it can be hard coded so the sales people can't change it, it appears to be the easiest for me to do and they can't monkey around with it.
    However, I like the thought of being able to pass a custom variable using a named range.
    I will read the links both of you reference and appreciate the information.
    Lee

Share This Page