none
Using Power Query to define date range RRS feed

  • Question

  • Hi,

    I'm very new to power query and I'd like to ask for a method to use a specific cell to define a date range. I have a query with date range as shown:

    --    a.LastUpdatedDate > @Daystart and a.LastUpdatedDate < @Dayend ( this one is just for day start and day end. Ignore this)
    --       TestExecutionTime > '2016-12-22 13:00:00.000'
    --   and TestExecutionTime < '2016-12-23 13:00:00.000'

    And I'd like to instead of manually having to come into editor everytime and change the date, just use a cell like say B2 and B3 to change the test execution time range. Is there a way to do this?

    Assume I know nothing about power query, links to someone else's solutions probably wont do me any good as I have tried to understand their situation and failed.

    Thanks!

    Wednesday, January 11, 2017 1:03 AM

Answers

  • Your query doesn't look like Power Query.

    But anyhow, steps to take:

    1. Setup a parameter table in Excel

    2. Create a connection to that table in Power Query.

    3. Create a Power Query function to return the value of a parameter.

    4. Use that function in your query to filter your data.

    I created a step-by-step instruction video for you: https://youtu.be/nZaJeglih8I

    Wednesday, January 11, 2017 7:14 AM

All replies

  • Your query doesn't look like Power Query.

    But anyhow, steps to take:

    1. Setup a parameter table in Excel

    2. Create a connection to that table in Power Query.

    3. Create a Power Query function to return the value of a parameter.

    4. Use that function in your query to filter your data.

    I created a step-by-step instruction video for you: https://youtu.be/nZaJeglih8I

    Wednesday, January 11, 2017 7:14 AM
  • This is brilliant, I will try to understand this procedure and experiment myself and see if it works!

    Thank you very much!

    Thursday, January 12, 2017 5:48 AM
  • Hi eugeneho1989,

    If the above solution worked for you, please don't forget to mark it as an answer :)


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Friday, January 27, 2017 7:14 AM
    Moderator