Number of current users with Multiple Placements

[davidmaddock54]

I have a client list Showing Client names and ref numbers, plus start and end dates for different programs and also user numbers for individual programs.

I want to get:

1. Number of current users involved in more than 1 program. (I have worked this out in PQ, as simple counts: 3 users in 3 programs, 140 users in 2 programs, the rest in 1 program.)
2. I can't work out how to present the data in a way that shows the actual client IDs and the specific current programs.
3. I would prefer to do it in PQ, as it'll be a monthly report and I'll be downloading the report from our client management software and putting it into a folder to update the PQ.

I have attached a Fake simplified version of the report with no PQ.

I'm almost 100% that the process involves grouping by overall user ID, and using Clients with no end date in program to work out who is current.

Any assistance appreciated.

[#2]

@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.

[#3]

Thanks so much, will attempt and let you know how I go.

[#4]

This appears to fail near the first hurdle. Unpivoting creates 2 columns, Attributes and Values.

[#5]

can you share your expected result according to the source table you have uploaded?

[#6]

@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.

[#7]

See my post below with updated sheet.