sorting data into hourly

[mikenz1983]

Hey Guys
i have a list of dates where calls have entered a business with time stamps. in excel i can apply a pivot to this data and group it by day and hour, it will then show me how many calls have been offered on each day and during each hour, it lists 8am, 9am etc

i am wanting to apply this same theory in powerbi, i am wanting to try and introduce a drop down whereby a date is selected and then the corresponding hourly data is displayed

any ideas ?

 

[severynm]

Add the date as a slicer visual. Changing the date in the slicer will then affect all visuals on the page by default (you can change this by Editing Interactions of the slicer). If the rest of your data is in a table or chart of some sort, those visuals will then be "filtered" to the selected slicer value.

 

[mikenz1983]

Add the date as a slicer visual. Changing the date in the slicer will then affect all visuals on the page by default (you can change this by Editing Interactions of the slicer). If the rest of your data is in a table or chart of some sort, those visuals will then be "filtered" to the selected slicer value.

Hey thanks so much for the advice
im struggling a little with this one so will give a bit more information, unless i am missing something

my excel table looks like this ( but alot bigger, in fact its probably 10,000 rows of data )

29/05/2020 16:56:48	0:07:51	0:00:10	0:07:38	00:13	Handled by Agent
29/05/2020 16:19:29	0:06:58	0:00:13	0:06:22	00:36	Handled by Agent
29/05/2020 16:19:16	0:00:11			00:11	Not handled by agent
29/05/2020 16:18:23	0:03:23	0:00:10	0:03:10	00:13	Handled by Agent
29/05/2020 16:16:57	0:11:23	0:00:13	0:11:08	00:15	Handled by Agent
29/05/2020 16:06:33	0:00:01			00:01	Unknown
29/05/2020 16:05:53	0:04:15	0:00:07	0:04:01	00:14	Handled by Agent
29/05/2020 16:04:42	0:01:09	0:00:04	0:01:02	00:07	Handled by Agent
29/05/2020 15:56:38	0:00:53	0:00:09	0:00:41	00:12	Handled by Agent
29/05/2020 15:45:32	0:03:38	0:00:09	0:01:40	00:12	Handled by Agent
29/05/2020 15:44:28	0:01:58	0:00:10	0:01:46	00:12	Handled by Agent
29/05/2020 15:42:35	0:00:34	0:00:09	0:00:23	00:11	Handled by Agent
29/05/2020 15:30:56	0:06:23	0:00:12	0:01:24	00:15	Handled by Agent
29/05/2020 15:27:32	0:03:23	0:00:04	0:01:55	00:06	Handled by Agent
29/05/2020 15:21:48	0:02:09	0:00:08	0:01:59	00:10	Handled by Agent
29/05/2020 15:10:45	0:04:56	0:00:07	0:04:02	00:10	Handled by Agent
29/05/2020 15:04:11	0:06:33	0:00:04	0:06:27	00:06	Handled by Agent

when i use excel i can pivot this and group by year then day then hour, which will show many calls ( this is a call centre ) came through each hour for each day of the year

the experience i am trying to go for, or close to, is a user be able to select a date and be presented with the daily interval volumes, so in this case they would somehow select 29/05/2020 and will be shown time periods ranging from 8am through to 4pm and each hour will have a volume next to it

the example of the pivot i have used in excel is below

1-Jul	270​
8 am	19​
9 am	39​
10 am	30​
11 am	38​
12 pm	29​
1 pm	30​
2 pm	37​
3 pm	30​
4 pm	18​
2-Jul	195​
8 am	12​
9 am	27​
10 am	22​
11 am	15​
12 pm	23​
1 pm	32​
2 pm	22​
3 pm	33​
4 pm	9​
3-Jul	232​
8 am	19​
9 am	29​
10 am	37​
11 am	29​
12 pm	21​
1 pm	26​
2 pm	29​
3 pm	27​
4 pm	15​

 

[alansidman]

Using your example and Power Query

Book12
	A	B	C
1	Date	Hour	Count Calls
2	5/29/2020	16	28
3	5/29/2020	15	36
Table1

Mcode for above solution.
l

et
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column1.1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Column1.1.1", "Column1.1.2", "Column1.1.3"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Split Column by Delimiter1", {{"Column1.1.2", type text}, {"Column1.1.1", type text}, {"Column1.1.3", type text}}, "en-US"),{"Column1.1.2", "Column1.1.1", "Column1.1.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Date"),
    #"Changed Type" = Table.TransformColumnTypes(#"Merged Columns",{{"Date", type date}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type", "Column1.2", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Column1.2.1", "Column1.2.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Column1.2.1", Int64.Type}, {"Column1.2.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Column1.2.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1.2.1", "Hour"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each List.NonNullCount(Record.FieldValues(_))-3),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Column2", "Column3", "Column4", "Column5", "Column6"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Date", "Hour"}, {{"Count Calls", each List.Sum([Custom]), type number}})
in
    #"Grouped Rows"

 

[mikenz1983]

Using your example and Power Query

Book12
	A	B	C
1	Date	Hour	Count Calls
2	5/29/2020	16	28
3	5/29/2020	15	36
Table1

Mcode for above solution.
l

et
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column1.1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Column1.1.1", "Column1.1.2", "Column1.1.3"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Split Column by Delimiter1", {{"Column1.1.2", type text}, {"Column1.1.1", type text}, {"Column1.1.3", type text}}, "en-US"),{"Column1.1.2", "Column1.1.1", "Column1.1.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Date"),
    #"Changed Type" = Table.TransformColumnTypes(#"Merged Columns",{{"Date", type date}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type", "Column1.2", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Column1.2.1", "Column1.2.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Column1.2.1", Int64.Type}, {"Column1.2.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Column1.2.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1.2.1", "Hour"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each List.NonNullCount(Record.FieldValues(_))-3),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Column2", "Column3", "Column4", "Column5", "Column6"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Date", "Hour"}, {{"Count Calls", each List.Sum([Custom]), type number}})
in
    #"Grouped Rows"

Hey

Thanks for the above, maybe i am being a bit novice here, so apologies if i start appearing so i am sorry.
i can do the view and sort in a pivot quite easily in excel, however what i am trying to get in powerbi is quite a different view

my source data in excel that i can import into power bi is every call no matter what the time, im trying to create a view with that data where someone can select a day and they will be presented with the daily hours ( in this case 8am through to 4pm ) and the call volume each hour they will expect next to it

im "ok" if it has to be a long list, but struggling to apply the above in power bi.

i have tried pasting this code into advanced editor in powerbi and just get an error thrown at me, happy to send screenshots if that helps

 

[severynm]

It seems like you're after something like this in Power BI: a dropdown (or something similar) to select the date, and then a table that shows the counts by hour.

And the m code that does this:

let
    Source = Excel.Workbook(File.Contents("C:\Users\%%%%%\Desktop\MrExcel.xlsm"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type1" = Table.TransformColumnTypes(Table1_Table,{{"Time3", type time}, {"Duration", type time}, {"Time2", type time}, {"Time1", type time}, {"Status", type text}, {"DateTime", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type1", {{"DateTime", type datetime}}, "en-CH"),
    #"Inserted Start of Hour" = Table.AddColumn(#"Changed Type with Locale", "Start of Hour", each Time.StartOfHour([DateTime]), type datetime),
    #"Inserted Start of Day" = Table.AddColumn(#"Inserted Start of Hour", "Start of Day", each Date.StartOfDay([DateTime]), type datetime)
in
    #"Inserted Start of Day"

Basically I just feed in the data table, change the data types so they are correct, and add two new columns using built in functions to PowerQuery: one with only the day (called "Start of Day") and one with only the hour (called "Start of Hour").

To put together the visuals in Power BI, create a Filter element and attached "Start of Day" as the field, and a Table with "Start of Hour" and "Status" as the values. It does not matter what field you use for the count, but whichever one you do use, you need to change the summarization to count.

 

[mikenz1983]

It seems like you're after something like this in Power BI: a dropdown (or something similar) to select the date, and then a table that shows the counts by hour.

And the m code that does this:

let
    Source = Excel.Workbook(File.Contents("C:\Users\%%%%%\Desktop\MrExcel.xlsm"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type1" = Table.TransformColumnTypes(Table1_Table,{{"Time3", type time}, {"Duration", type time}, {"Time2", type time}, {"Time1", type time}, {"Status", type text}, {"DateTime", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type1", {{"DateTime", type datetime}}, "en-CH"),
    #"Inserted Start of Hour" = Table.AddColumn(#"Changed Type with Locale", "Start of Hour", each Time.StartOfHour([DateTime]), type datetime),
    #"Inserted Start of Day" = Table.AddColumn(#"Inserted Start of Hour", "Start of Day", each Date.StartOfDay([DateTime]), type datetime)
in
    #"Inserted Start of Day"

Basically I just feed in the data table, change the data types so they are correct, and add two new columns using built in functions to PowerQuery: one with only the day (called "Start of Day") and one with only the hour (called "Start of Hour").

To put together the visuals in Power BI, create a Filter element and attached "Start of Day" as the field, and a Table with "Start of Hour" and "Status" as the values. It does not matter what field you use for the count, but whichever one you do use, you need to change the summarization to count.

Hey

Thanks for the assistance, this is absolutely closer to what i am looking for, i keep on coming back with an error and unsure why

this is my table: ( column i has start of day and j has start of hour )

The code i am using is this:

let
Source = Excel.Workbook(File.Contents("C:\Users\MichaelLe\Desktop\Up to date Call Data.xlsx"), null, true),
Table1_Table = Source{[Item="Calls",Kind="Sheet"]}[Data],
#"Changed Type1" = Table.TransformColumnTypes(Calls_Table,{{"Date", type time}, {"Month", type time}, {"Date", type time}, {"Date", type time}, {"Status", type text}, {"DateTime", type text}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type1", {{"DateTime", type datetime}}, "en-CH"),
#"Inserted Start of Hour" = Table.AddColumn(#"Changed Type with Locale", "Start of Hour", each Time.StartOfHour([DateTime]), type datetime),
#"Inserted Start of Day" = Table.AddColumn(#"Inserted Start of Hour", "Start of Day", each Date.StartOfDay([DateTime]), type datetime)
in
#"Inserted Start of Day"


the visuals i know will be pretty easy once i nail this part of it, appreciate the assistance so far

Regards

 

[severynm]

What does the error say? And when does it ocurr? From my experience in Power BI the errors often do a good job of leading me to exactly what the issue is.

 

[mikenz1983]

What does the error say? And when does it ocurr? From my experience in Power BI the errors often do a good job of leading me to exactly what the issue is.

it varies as i work through it

the first is:

Expression.Error: The import Calls_Table matches no exports. Did you miss a module reference?

 

[severynm]

At first glance, it looks like these need to be the same name:

Table1_Table = Source{[Item="Calls",Kind="Sheet"]}[Data],
#"Changed Type1" = Table.TransformColumnTypes(Calls_Table,{{"Date", type time}, {"Month", type time}, {"Date", type time}, {"Date", type time}, {"Status", type text}, {"DateTime", type text}}),