locked
Help with a PowerPivot query RRS feed

  • Question

  • Hi,

    I've got a server audit log in PowerPivot (114,000 rows) which I provide some basic reporting from (PowerPivot is not my specialist subject so go easy).  What I now need to perform a Distinct filter, but can't get the logic right. Basically the log as two bits of information, 1) is the source of the job and 2) if it was a success or failure.    What I need to do is to find all of the Source items that have never completed sucessfully.  Any ideas on what formula is best to use, or do I need to split in to multiple sheets and create a table relationship?

    Source,Status
    ======,======
    Source1,Fail
    Source1,Fail
    Source1,Success
    Source2,Fail
    Source3,Fail
    Source3,Success

    So in the above example, I would only expect the filter to show me Source2

    Thanks

    Alex

    Thursday, April 2, 2015 1:57 PM

Answers

  • Hi Alex,

    using Power Query instead, it would work like this:

    Once you've pushed your table to PQ, add a column "helper" with value=1.

    Then pivot on "Status", taking "helper"-column into "values column"

    (hope translation is understandable, as I'm using localized Version & translation via "Quick info" isn't working in the PQ menu)

    Then filter on column "Success" = blank.


    Imke


    Thursday, April 2, 2015 7:23 PM
    Answerer
  • thanks Imke,

    I will give this a go

    cheers Alex

    al

    Wednesday, April 8, 2015 7:05 AM

All replies

  • Hi Alex,

    using Power Query instead, it would work like this:

    Once you've pushed your table to PQ, add a column "helper" with value=1.

    Then pivot on "Status", taking "helper"-column into "values column"

    (hope translation is understandable, as I'm using localized Version & translation via "Quick info" isn't working in the PQ menu)

    Then filter on column "Success" = blank.


    Imke


    Thursday, April 2, 2015 7:23 PM
    Answerer
  • thanks Imke,

    I will give this a go

    cheers Alex

    al

    Wednesday, April 8, 2015 7:05 AM