Correct attendance data format to run analysis

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 🙂

YouTube  LinkedIn