If you need to analyze user activity by site (metadata) and generate reports such as:
- Number of users per site
- Last recent activity
- Percentage of users per site
- Top 10 sites with the most users
You can achieve this using Excel’s Power Query to transform exported user data. Follow the steps below:
Step 1: Export User Data from nomorePAPER
1. Log in to your nomorePAPER admin dashboard.
2. Navigate to Users and export the user list (typically as a CSV or Excel file).
3. Save the file to your computer.
Step 2: Open the File in Excel and Load into Power Query
1. Open the exported file in Microsoft Excel.
2. Select the entire data range (including headers).
3. Go to the Data tab in the ribbon.
4. Click From Table/Range (this opens Power Query Editor).
- If prompted, ensure "My table has headers" is checked and click OK.
Step 3: Clean and Split the Metadata Column
A. Trim Whitespace (Optional but Recommended)
1. In Power Query, right-click the Metadata column.
2. Select Transform > Trim (removes extra spaces before/after metadata values).
B. Split Metadata by the "|" Delimiter into Rows
1. With the Metadata column still selected, go to the Home tab.
2. Click Split Column > By Delimiter.
3. In the dialog box:
- Select or enter delimiter: Choose Custom and type `|`
- Under Advanced Options, select Split into Rows (this ensures users with multiple sites appear in separate rows).
4. Click OK.
Step 4: Group Data to Count Users per Site
1. Go to the Home tab.
2. Click Group By.
3. In the Group By window:
- Group by: Select the Metadata column (this represents the site names).
- Operation: Choose Count Rows (this gives the number of users per site).
- (Optional) Add additional aggregations (e.g., Max of "Last Activity Date").
4. Click OK.
Step 5: Load the Transformed Data Back to Excel
1. Click Close & Load (sends the processed data to a new worksheet).
2. You now have a clean breakdown of users per site and can:
- Create charts (e.g., pie chart for % of users per site).
- Sort to find top 10 sites by user count.
- Filter by date for recent activity analysis.
Additional Tips
- Pivot Tables: Use the transformed data to create pivot tables for deeper analysis.
- Refreshing Data: If the source export updates, right-click the Power Query output and select Refresh to pull in new data.
- Multiple Metadata Columns: If metadata has multiple attributes (e.g., `Site|Department`), repeat the split process for each delimiter.
Example Output Structure
| Site (Metadata) | User Count | Last Activity Date |
|---------------------|--------------|-----------------------|
| Nashville | 45 | 2024-05-15 |
| Memphis | 32 | 2024-05-10 |
| Knoxville | 28 | 2024-05-12 |
---
This method ensures accurate reporting even when users belong to multiple sites. Let us know if you need further assistance!