locked
Query query help RRS feed

  • Question

  • Hi,  i have a query that I can't figure out and any help would be greatly recieved.

    I have a table that looks like this with approx 300,000 items. 

    Site, File, Last modified person, Last modified date.

    ================================

    Site1, File1, Person1, 10/01/2010

    Site1, File2, Person2, 10/01/2011

    Site1, File3, Person3, 10/01/2012

    Site1, File4, Person4, 10/01/2013

    Site2, File1, Person1, 12/03/2013

    Site2, File2, Person2, 10/01/2014

    - I need to get the last 3 people to modify the site, then transform the sites in to Columns so that it looks like the followin

    Person, Column1, Column2, Column3, etc.

    ==========

    Person1, Site1, Site2

    Person2, Site1

    Is this possible in PQ?

    Thanks

    Alex

    Thursday, June 11, 2015 1:57 PM

Answers

  • You could extend these measures to only show the top 3 ranked items in your table with an IF statement. I will try and think of other ways to do this.

    MaxDate:=MAX(Sheet1[Date])
    Rank Within Site:=IF(HASONEVALUE(Sheet1[Site]),RANKX(ALL(Sheet1[Prson]),[MaxDate]),BLANK())

    Then in your table has Site in your columns and person in row.

    • Marked as answer by Charlie Liao Wednesday, June 24, 2015 2:08 AM
    Thursday, June 11, 2015 4:55 PM