How to create a Headcount by Hour for employees of multiple positions using Power Query to Automatically refresh data.

[mtfedder]

I have data for a couple hundred employees each day mainly position, date, shift start, shift end. I need to create a bar chart showing the total count by position per hour. The part I am having trouble with is getting excel to recognize shift start and shift end as a time range and to count once in each hour. I am also trying to do this through power query to auto-update the data each day we update shift information. Can anyone help? I have come across "countIF" statements for existing tables, but couldn't translate it to my dataset in power query. Any suggestions? (Asking as a bit of a novice so explain each step if you could)

 

[GraH]

if data is like this (pivot is the result of the PQ)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3		name	position	date	shift start	shift end			Count of Unique Hrs Worked	Column Labels
4		a	Px	2/09/2021	2:42:03	7:04:04			Row Labels	1/09/2021	2/09/2021	3/09/2021	4/09/2021	5/09/2021	6/09/2021
5		a	Px	3/09/2021	2:02:26	8:02:56			0		1		1	1	1
6		a	Px	1/09/2021	7:32:42	11:29:24			1		1		1	1	1
7		a	Px	5/09/2021	5:03:00	11:38:47			2	1	1	1	1	1	1
8		b	Px	5/09/2021	1:42:36	8:53:17			3	1	1	1	1	1	1
9		c	Px	5/09/2021	15:56:25	22:45:08			4	1	1	1	1	1
10		b	Px	3/09/2021	3:31:18	8:46:46			5	1	1	1	1	1
11		c	Px	1/09/2021	19:43:43	3:23:29			6	1	1	1	1	1
12		a	Px	4/09/2021	3:23:43	8:17:21			7	1	1	1	1	1
13		b	Px	2/09/2021	13:08:09	18:48:05			8	1	1	1	1	1
14		c	Px	5/09/2021	21:41:49	3:53:31			9	1	1		1	1
15		c	Px	4/09/2021	7:38:42	12:09:01			10	1	1		1	1
16		c	Px	3/09/2021	15:26:29	23:01:38			11	1	1		1	1
17		d	Px	5/09/2021	5:32:22	11:05:44			12		1		1
18		e	Px	5/09/2021	4:12:09	8:21:11			13		1
19		e	Px	3/09/2021	23:48:41	5:13:13			14		1
20		b	Px	1/09/2021	17:39:48	23:05:59			15		1	1		1
21		d	Px	4/09/2021	19:02:13	1:22:06			16		1	1		1
22		c	Px	2/09/2021	5:05:53	12:41:05			17	1	1	1		1
23		d	Px	1/09/2021	2:05:06	6:57:24			18	1	1	1		1
24									19	1		1	1	1
25									20	1		1	1	1
26									21	1		1	1	1
27									22	1		1	1	1
28									23	1		1	1	1
29									Grand Total	17	19	16	18	21	4
30
31
Sheet1

then this query might work

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"position", type text}, {"date", type datetime}, {"shift start", type time}, {"shift end", type time}}),
    OverNight = Table.AddColumn(#"Changed Type", "OverNightShift", each [shift end]<[shift start]),
    GetHrs = Table.AddColumn(OverNight, "Hours Worked", each let
 Hrs1 = if [OverNightShift] = false then {{Time.Hour([shift start]) .. Time.Hour([shift end])}, {}} else
 { {Time.Hour([shift start]) .. 23}
 , {0 .. Time.Hour([shift end]) } }
in
Hrs1),
    HrsOnDate = Table.AddColumn(GetHrs, "HrsOnDate", each [Hours Worked]{0}),
    HrsOnNextDate = Table.AddColumn(HrsOnDate, "HrsOnNextDate", each [Hours Worked]{1}),
    AddNextDate = Table.AddColumn(HrsOnNextDate, "NextDate", each if [OverNightShift] = true then Date.AddDays([date],1) else null, Date.Type),
    ExtractHrs1 = Table.TransformColumns(AddNextDate, {"HrsOnNextDate", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    ExtractHrs2 = Table.TransformColumns(ExtractHrs1, {"HrsOnDate", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    Remove1 = Table.RemoveColumns(ExtractHrs2,{"Hours Worked", "HrsOnDate", "date"}),
    Day2Table = Table.Buffer(Table.RenameColumns(Remove1,{{"NextDate", "date"}, {"HrsOnNextDate", "HrsOnDate"}})),
    Day1Table = Table.Buffer(Table.RemoveColumns(ExtractHrs2,{"Hours Worked", "HrsOnNextDate", "NextDate"})),
    TableD1D2 = Table.Combine({Day1Table,Day2Table}),
    #"Filtered Rows" = Table.SelectRows(TableD1D2, each ([date] <> null)),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Filtered Rows", {{"HrsOnDate", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "HrsOnDate"),
    #"Grouped Rows" = Table.Group(#"Split Column by Delimiter", {"date"}, {{"AllDay", each _, type table [name=nullable text, position=nullable text, date=datetime, shift start=nullable time, shift end=nullable time, OverNightShift=logical, HrsOnDate=nullable text]}}),
    GetUniqueHrsPerDay = Table.AddColumn(#"Grouped Rows", "Unique Hrs Worked", each List.Sort(List.Distinct([AllDay][HrsOnDate]))),
    RemoveGroupBy = Table.RemoveColumns(GetUniqueHrsPerDay,{"AllDay"}),
    #"Expanded Unique Hrs Worked" = Table.ExpandListColumn(RemoveGroupBy, "Unique Hrs Worked"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Unique Hrs Worked",{{"Unique Hrs Worked", Int64.Type}, {"date", type date}})
in
    #"Changed Type1"