Sorting Data from JIRA

[Dorse411]

Evening all.
I have been tasked with sorting out a report that comes out of jira kanban into a format that our timesheet system can read, any ideas/suggestions would be greatly appreciated.
report from jira outputs like so

Issue	Project	Task Code	Date	Hours
floppy disk dead	620	11	14/01/2020	2
crt monitor exploded	635	69	14/01/2020	3
C64 wont play farcry 10	666	6	15/01/2020	1

Need it to out put into something like so

Project	Task Code	Monday	Tuesday	Wednesday	Thursday	Friday
620	11	0	2	0	0	0
635	69	0	3	0	0	0
666	6	0	0	1	0	0

will need to add totals to the bottom but I can do that, and i know the formula to turn the numbered date into text, as well as copying to a new sheet.

My issue is getting the data and spliting it up into the days of the week.

Any ideas?

 

[sandy666]

with Power Query

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Issue", type text}, {"Project", Int64.Type}, {"Task Code", Int64.Type}, {"Date", type date}, {"Hours", Int64.Type}}),
    Day = Table.AddColumn(Type, "Day Name", each Date.DayOfWeekName([Date]), type text),
    Pivot = Table.Pivot(Day, List.Distinct(Day[#"Day Name"]), "Day Name", "Hours", List.Sum),
    Sort = Table.Sort(Pivot,{{"Project", Order.Ascending}})
in
    Sort

there is no more days because column Date doesn't contain more than you see
column Date can be removed from the result