Novice Guidance

[ylafont]

For the past few years, I have been manually tracking data reporting on user activity. it hasn't been bad since it is not performed excessively and I want to try to automate it in some manner to alleviate the burden and i thought this was the perfect to to understand and learn BI.

Data is exported daily as Excel files and kept in a folder, here is a modified sample, there are many columns and we are only displaying the ones needed for this exercise.


To track and report daily user activity, I have been manually transposing the data like so.


With this, I can track daily users/group performance and retain a historical log. Note not every user has an activity every day and some days have no activity.

The goal was for me to somehow automate a daily report, can anyone provide guidance on how to perform this in BI or alternative solution? thank you in advance!

 

[SanjayGMusafir]

I don't have exposure to Power BI but looking at your data Pivot Tables can also be a solution. The only challenge is how data is updated in a file on daily basis.

 

[alansidman]

Using Power Query within PBI

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Date", type text}}, "en-US")[Date]), "Date", "Value", List.Sum)
in
    #"Pivoted Column"

MOCK_DATA.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Date	first_name	Value		first_name	9/15/2023	9/16/2023	9/17/2023	9/18/2023	9/19/2023	9/20/2023	9/21/2023	9/22/2023	9/23/2023	9/24/2023	9/25/2023	9/26/2023	9/27/2023	9/28/2023	9/29/2023	9/30/2023
2	9/15/2023	user1	63.38		user1	63.38	80.2	3.37	25.29	90.71	94.46	85.38	7.72	70.26	44.95	58.98	32.39	43.11	11.17
3	9/16/2023	user1	80.2		user2	19.96	79.29	36.76	51.03	22.51	96.05	14.07	99.26	27.92	52.43	22.64	67.29	61.82	70.81	103.2	51.8
4	9/17/2023	user1	3.37
5	9/18/2023	user1	25.29
6	9/19/2023	user1	90.71
7	9/20/2023	user1	94.46
8	9/21/2023	user1	85.38
9	9/22/2023	user1	7.72
10	9/23/2023	user1	70.26
11	9/24/2023	user1	44.95
12	9/25/2023	user1	58.98
13	9/26/2023	user1	32.39
14	9/27/2023	user1	43.11
15	9/28/2023	user1	11.17
16	9/29/2023	user2	90.68
17	9/30/2023	user2	44.27
18	9/15/2023	user2	19.96
19	9/16/2023	user2	79.29
20	9/17/2023	user2	36.76
21	9/18/2023	user2	51.03
22	9/19/2023	user2	22.51
23	9/20/2023	user2	96.05
24	9/21/2023	user2	14.07
25	9/22/2023	user2	99.26
26	9/23/2023	user2	27.92
27	9/24/2023	user2	52.43
28	9/25/2023	user2	22.64
29	9/26/2023	user2	67.29
30	9/27/2023	user2	61.82
31	9/28/2023	user2	70.81
32	9/29/2023	user2	12.52
33	9/30/2023	user2	7.53
data