Forum Discussion
Solved
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 ...
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 🙂
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 🙂
