Number of current users with Multiple Placements

davidmaddock54 

Have realised I messed up in my demo sheet. The issue is that the Player Names are repeated for each individual position. So if someone is a PG and an SG, they have 2 lines. I have added some examples of how I was filtering the data, and what I want it to look like.

davidmaddock54 

To achieve the desired results using Power Query (PQ), you can follow these steps:

1. Open Microsoft Excel and go to the "Data" tab.
2. Click on "From Table/Range" to import the data into Power Query.
3. In the Power Query Editor window, you'll see the data in a tabular format.
4. Select the columns "Point Guard," "Shooting Guard," "Small Forward," "PG Placement Start," "PG Placement End," "SG Placement Start," and "SG Placement End."
5. Right-click on any of the selected columns and choose "Unpivot Other Columns." This will transform the data into a normalized structure.
6. Rename the generated columns to "User," "Program," "Placement Start," and "Placement End."
7. Remove any rows where the "User" or "Program" columns are empty.
8. Convert the "Placement Start" and "Placement End" columns to date format if they aren't already. Select the columns, go to the "Transform" tab, and choose the appropriate date format from the dropdown menu.
9. Add a custom column by clicking on "Add Column" in the Power Query Editor toolbar and selecting "Custom Column."
10. In the formula bar, enter the following formula to check if a program is current:

= if [Placement End] = null or [Placement End] >= DateTime.LocalNow() then "Current" else "Past"

11. Name the custom column "Status."

12. Group the data by the "User" column. Go to the "Home" tab and click on "Group By.

13. In the Group By dialog box, select the "User" column, and click on the "Advanced" button.

14. In the Advanced Group By dialog box, add a new aggregation for the "Program" column. Choose the "All Rows" option from the dropdown menu.

15.Click OK to apply the grouping.

16. Expand the grouped data by clicking on the double arrow icon next to the "Program" column. This will show the expanded rows with the individual programs for each user.

17. Add a new custom column named "Number of Programs" with the following formula:

= Table.RowCount([Program])

19. Filter the data by the "Number of Programs" column to exclude users with only one program.

20. Select the columns you want to include in the final report, such as "User," "Program," "Status," and any other relevant columns.

21. Load the data back into Excel by clicking on "Close & Load" in the Power Query Editor.

This process will provide you with a table that shows the client IDs, specific current programs, and the number of current programs each client is involved in. The steps were created with the help of AI. The file was not opened for personal security reasons.