Forum Discussion
Correct attendance data format to run analysis
Hi all,
I received the file i have uploaded in this link and i was tasked to provide an intuitive PBI solution for attendance visualization with only this file provided.
I am new in Power bi and would like to format the data i have in a way that can be used into Power Bi. The sheet = office totals can be unpivoted easily but i have NO idea how i can deal with the sheet = Home and office work schedule.
If someone can please help me or show me how i can transform the data in a usable format for analysis on Power Bi i would be gratefull. I would rather have all changes made on Power Bi but i assume some changes need to be done in Excel beforehand.
Mous007
Hi, you can trasnform the sheet as I have done below:
Go to Query Editor and paste the following code in a blank Query and adjust he path of the file.let Source = Excel.Workbook(File.Contents("C:\Users\Fowmy\Downloads\Sample data.xlsx"), null, true), #"home and office work schedule_Sheet" = Source{[Item="home and office work schedule",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(#"home and office work schedule_Sheet", [PromoteAllScalars=true]), #"Promoted Headers1" = Table.PromoteHeaders(#"Promoted Headers", [PromoteAllScalars=true]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers1", {"Name", "Team"}, "Attribute", "Value"), #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Attribute", type date}}), #"Inserted Day Name" = Table.AddColumn(#"Changed Type", "Day Name", each Date.DayOfWeekName([Attribute]), type text), #"Renamed Columns" = Table.RenameColumns(#"Inserted Day Name",{{"Attribute", "Date"}, {"Value", "Location"}}) in #"Renamed Columns"________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
3 Replies
Hello Mous007
this depends what exactly you want to show. But if you would like to join this two datasources you have to do the following steps
Totals-sheet
- get a timeline on your office totals-sheet
- extract the teamname in a new column
- Get rid of your totals row
- Unpivot all columns other than the team name
Home and ... sheet
- use the date-row as header
- Unpivot all columns other than team name and employee
no you can join both tables by the Team-column
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyMous007
Hi, you can trasnform the sheet as I have done below:
Go to Query Editor and paste the following code in a blank Query and adjust he path of the file.let Source = Excel.Workbook(File.Contents("C:\Users\Fowmy\Downloads\Sample data.xlsx"), null, true), #"home and office work schedule_Sheet" = Source{[Item="home and office work schedule",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(#"home and office work schedule_Sheet", [PromoteAllScalars=true]), #"Promoted Headers1" = Table.PromoteHeaders(#"Promoted Headers", [PromoteAllScalars=true]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers1", {"Name", "Team"}, "Attribute", "Value"), #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Attribute", type date}}), #"Inserted Day Name" = Table.AddColumn(#"Changed Type", "Day Name", each Date.DayOfWeekName([Attribute]), type text), #"Renamed Columns" = Table.RenameColumns(#"Inserted Day Name",{{"Attribute", "Date"}, {"Value", "Location"}}) in #"Renamed Columns"________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
