How to organize this so that it makes sense?

[telusive]

My company wants to turn this into a dashboard, it currently monitors the amount of hours per phase by employee of projects. However, as you can see, its a mess in excel. I've been transposing it and trying to make it make sense, but I'm failing. Ideally the output would be something like:

                 7/26/2024         7/29/2024
Jack              24                         40
Jill                 34                         44
Mac               40                          25


but for every week. Currently the employee name is one line above Task. I keep running into issues including the week start and end AND getting the project name/ID and task together or in 2 tables. Any help would be appreciated!!

Thanks!!

[telusive]

Yes, this worked perfectly. Thanks so much for your time, I learned a ton!!

[dufoq3]

You're welcome 😉

[dufoq3]

Hi, delete/replace sensitive data from your excel file and upload your file i.e. to google drive. Provide here a link (with public permissions).

[telusive]

https://docs.google.com/spreadsheets/d/1wMfjuxnZxLk0oixLS41ySNRmFmjVNdl2/edit?usp=sharing&ouid=10442...

Does this work?

[dufoq3 · Accepted solution]

Is this what are you looking for? You can put this into pivot table or power bi report as source data.

Just replace address to your source excel file.

Result

let
    Source = Excel.Workbook(File.Contents("C:\Downloads\PowerQueryForum\telusive\FAbric.xlsm"), null, true),
    Detail_Sheet = Source{[Item="Detail",Kind="Sheet"]}[Data],
    #"Removed Top Rows" = Table.Skip(Detail_Sheet,2),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Removed Top Rows1" = Table.Skip(#"Promoted Headers",8),
    #"Filled Down" = Table.FillDown(#"Removed Top Rows1",{"Column1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Filled Down",{{"Column1", "Nam"}, {"Column3", "Description"}, {"Column4", "Project"}, {"Column5", "No"}, {"Week", "Client"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Column2"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Project] <> null)),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Nam", "Description", "Project", "No", "Client"}, "Week", "Hours"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Week", Int64.Type}, {"Hours", type number}})
in
    #"Changed Type"