+ Reply to Thread
Results 1 to 3 of 3

Need to convert into PP (PowerPivot)

  1. #1
    Forum Contributor
    Join Date
    08-29-2017
    Location
    Canada
    MS-Off Ver
    Window 2013
    Posts
    112

    Need to convert into PP (PowerPivot)

    Hi experts,

    I have an attached workbook with the issue already solved in traditional formula methods, can you show me or convert that excel into a Powerpivot with the same exact result? i may have raw data getting into the 700K range.


    Thank you,

    Noah
    Attached Files Attached Files

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016
    Posts
    4,821

    Re: Need to convert into PP (PowerPivot)

    First, let's use Power Query to structure the data we need.

    1. Format your source data as a table, and create a query which references this table
    RawData:
    Please Login or Register  to view this content.
    2. Now we can create a list of all possible start times, between the earliest and latest start times in your data, based on 30 min intervals:
    TimeList:
    Please Login or Register  to view this content.
    3. Close and Load both queries as 'Connection Only', and 'Load to Data Model'

    4. In PowerPivot, create a relationship between RawData[Start Time] and TimeList[Start Time]

    5. Add the following measure (you can add in either table,m I added it to TimeList, then hid RawData from client tools)
    Please Login or Register  to view this content.
    6. Insert a pivot table, with Start Time and End Time as rows, and StaffCount as Values. Format to suit (tabular form, no subtotals, etc)

    See attachment for worked example.
    Attached Files Attached Files
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Forum Contributor
    Join Date
    08-29-2017
    Location
    Canada
    MS-Off Ver
    Window 2013
    Posts
    112

    Re: Need to convert into PP (PowerPivot)

    Wow amazing! Thank you, I will look at this more, looks very good. The code above did you have to manually write that code or did that code build itself when you fashioned the relelationships in PP?

    --- Noah

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1