Count of columns with date per person

[Anonymous]

Hi All

need help. i have an export of a training module program and i need to summarise the data in different reports

i have 200 users and over 100 training modules exported in a spreadsheet. Format of excel as below (but in a larger scale)

the training columns are not labelled as 1, 2, 3 etc, they are the names of the actual modules

if the module was completed i get a date stamp and a blank if no completion to date

User Name	Training 1	Traning 2	Training 3	....	Traning 100
User 1	12/07/2021
User 2		12/07/2021	12/07/2021
...
User 200

i need to summarise the data as

- count how many modules completed by each user
- count how many users have completed each module so i can show popular to non popular modules (somehow show this in a report/table without having to drag in 100+ columns)

thankyou in advance

[amitchandak · Accepted solution]

@Anonymous , I know data would increase , but unpivot thee columns. Then you will have these in rows. Remove rows with blank timestamp if needed

https://radacad.com/pivot-and-unpivot-with-power-bi