cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mous007
Helper IV
Helper IV

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.

 

 

1 ACCEPTED SOLUTION
Fowmy
Super User IV
Super User IV

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

Fowmy_0-1598985744535.png

 

________________________

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 🙂

YouTube  LinkedIn

 

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

3 REPLIES 3
Fowmy
Super User IV
Super User IV

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

Fowmy_0-1598985744535.png

 

________________________

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 🙂

YouTube  LinkedIn

 

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

Managed to solve the issue (regional settings) and gotthe same format as @Fowmy 

 

Thank you both.

 

 

 

 

Jimmy801
Super User III
Super User III

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

Jimmy

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors