How to data transfer from attendeance raw list to more meaningful data for report

[EdwardY]

I am a Power BI desktop begineer. I want to get your advice on data handling for attendance table. I got a single column with 3 status (Present, Abense, Sickleave)

I want to use Y-axis to count number of people status (e.g. Present), X-axis present different team, within 1 month (they attend class once a week). How should I handle data transfer before visualize my data ? OR I do not need to handle data tansafer before c

Name	Date	Status	belongs group
Peter	2020 Sept 02	Present	Group A
Ms. Cheung	2020 Sept 02	Present	Group A
Jon	2020 Sept 02	Absence	Group A
Marry	2020 Sept 02	Sick	Group A
Peter	2020 Aug 26	Present	Group A
Ms. Cheung	2020 Aug 26	Absence	Group A
Jon	2020 Aug 26	Present	Group A

Chart

Y axis (count no. of attendance)

  X asix (individual group) around difference time slot.

Group	Team Name	Date	Attendance	Absence	Address
S1	Happy Team	2020-09-12	2	0	Downtown
S1	Zippy Team	2020-09-12	1	0	Downtown
S2	Outreach team	2020-09-12	2	0	Downtown
S3	Love Pet Team	9/10/2020	2	0	Beach Aside

[v-diye-msft]

Hi @EdwardY 

If the above posts help, please kindly mark it as a answer to help others find it more quickly. thanks!

If not, please kindly elaborate more.

[Greg_Deckler]

@EdwardY Sorry, having trouble following, maybe an image of what you are trying to achieve? Could be that I just woke up. If your first table is source data then it looks OK to me in that form. Not sure what the second table represents, is that your expected output? I'm not clear on the X / Y chart what you want exactly but you shouldn't have any problem producing it.

Since this is dealing with attendance you may be interested in this. https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

@ me in replies. Thanks.

[EdwardY]

Sorry, I am first time to post message on that forums. It seems forum got some restriction and remove my Group column on my 1st post.

Table 1:

I just want to transfer data from table 1 to the following table 2. (Summarize members on team first and present under Group)

Table 2

[Greg_Deckler]

@EdwardY OK, so see the attched PBIX file and see if it fits the need. PBIX is attached below sig. You want Table (14) and Table (14a) I am still not clear on the ultimate goal and whether or not it can be achieved without an intermediate table but this should get you the table. You could probably also produce this second table using a Refernce in Power Query to create a second query and do some Group By operations. @ImkeF and @edhans can help there if necessary.

Table (14a) = 
    SUMMARIZE(
        'Table (14)',
        [Group],[Team Name],[Date],
        "Attendance",COUNTROWS('Table (14)'),
        "Absence",COUNTROWS(FILTER('Table (14)',[Status]="Absence")),
        "Address",MAX('Table (14)'[Address])
    )

[EdwardY]

@Greg_Deckler  Thank you very much, you are so quick to handle my case.

Finally, def. I want to prepare Group1, summary ( number of attendance) from each Team, summary ( number of absence).  Later I could make a good chart on it.  Table 2 is a sort of micro-managment how many student attend on small team and trace which small team need to help from teacher.

I do not want put my question on forum and let people answer all of them. I need to learn how to solve it also. 

Any suggestion book for beginner of Power BI. Please let me know it.  I found it is very powerful tool for use, but need spend time and your super help !

Thank you again!

[AllisonKennedy]

@EdwardY

Here's a link to the Radacad that @Greg_Deckler mentioned:
https://learn.radacad.com/?ref=818

[Greg_Deckler · Accepted solution]

@EdwardY First, happy to help! Second, I should have a standard answer for this question! So, people learn in differnt ways. I learn by doing so I started following this forum 5 years ago trying to answer questions and that's how I learned. Still learning.

If you want guided learning I would start here: https://powerbi.microsoft.com/en-us/learning/

https://docs.microsoft.com/en-us/power-bi/guided-learning/

There are tons of great blogs, too many to list but radacad specializes in teaching Power BI and offers hands-on, guided training.

sqlbi.com is kind of the technical, in-depth power house of DAX knowledge

In terms of books, I have 2, one is Learn Power BI and it is fine except that the screen shots of the interface are a little out-dated. Microsoft moved some stuff around. I also have DAX Cookbook, this book shows common problems from a business perspective and how to solve them with DAX.

@marcorusso 's book Definitive Guide to DAX is exactly what it says. I also like @Phil_Seamark 's book Beginning DAX (especially good if coming from a SQL background and @MattAllington 's books are great too.

I am sure there is more great stuff out there that I am forgetting to mention and for that I apologize to any who feels slighted!! 😄

[Greg_Deckler]

@EdwardY OK, I am mocking up this data. So to answer your question, I think, no, you probably do not want to transform or summarize this data before hand in Power Query although I could be wrong because I'm not 100% on your use cases at the moment but we can get a working copy and go from there. Unless you want to just post a link to your PBIX file and source file on OneDrive or somthing.