none
Create Dynamic Table RRS feed

  • Question

  • Hello,

    I would like to create a dynamic table using power query.  For example, my current report is set to automatically refresh and will show the number of tickets that are open at that time.  What I would like to do, is create a table that is dynamic, so that it is able to be trended.  Are there any recommendations or ideas on how to do this?

    Thanks,

    Nick

    Monday, July 27, 2015 11:33 PM

Answers

All replies

  • That's not much to go on. Are you saying that you want keep a history of tickets that are open on any specific date? If that is the case, you will need your query, or a separate query to bring in all tickets with their open and close dates. Then, you could create measures to determine how many tickets were open on a specific date, open date <= selected date and close date >= selected date.

    If this is not what you are looking for, more information and sample data please.

    Tuesday, July 28, 2015 1:44 AM
  • Have a look at this blogpost:

    http://blog.oraylis.de/2015/05/incremental-data-loads-in-microsoft-power-query-for-excel/

    Since it is now possible to feed a query directly from the result of another query, you can actually keep the results of your previous queries & append the new ones. - Provided everything will still fit in an Excel sheet (< 1,1 Mio rows).


    Imke

    Tuesday, July 28, 2015 6:15 AM
    Moderator
  • Thanks - I realize what I posted was not incredibly helpful.  I've attached a sample dataset.

    How can I:

    - create a separate query based on date to bring in all tickets with open & closed date?

    -create the calculated measures?

    I appreciate all of your help!  I hope this provides more context.

    Here is a link to the dataset:https://onedrive.live.com/redir?resid=2F32CE379E2115FD!113&authkey=!AKIzpdJQBBpiRXk&ithint=file%2cxlsx

    -Nick


    Tuesday, July 28, 2015 3:47 PM
  • Thanks - I realize what I posted was not incredibly helpful.  I've attached a sample dataset.

    How can I:

    - create a separate query based on date to bring in all tickets with open & closed date?

    -create the calculated measures?

    I appreciate all of your help!  I hope this provides more context.

    Here is a link to the dataset:https://onedrive.live.com/redir?resid=2F32CE379E2115FD!113&authkey=!AKIzpdJQBBpiRXk&ithint=file%2cxlsx

    -Nick

    Tuesday, July 28, 2015 3:51 PM
  • OK, not too sure if I got your request right, but this might help for what I've understood so far:

    http://www.powerpivotpro.com/2013/04/counting-active-rows-in-a-time-period-guest-post-from-chris-campbell/

    this would also be possible in PQ (crossjoin with a calender table/list), but I think PP works better here.


    Imke

    Wednesday, July 29, 2015 5:48 PM
    Moderator