Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User

@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
Super User

@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

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

 

Thank you both.

 

 

 

 

Jimmy801
Community Champion
Community Champion

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors