Remodel an excel report to suit Power Bi

[SamBrown17]

Hi Team,

I am not sure if this can be done. but a have a snippet of the below Table.  I want to be able to report on the different User names and the number of call types and the average times. is there a way to pull this apart and rebuild it in Power bi so I can get the number of Inbound calls by a user etc.

Type Of Call	Answered Count	Abandoned Before Minimum Count	Abandoned After Minimum Count	Talk Time Total	Talk Time Maximum	Talk Time Average
222  User Name 1
Count of Type of Call	Number	Type Of Call
Incoming	5	0	317	0 00:07:57	0 00:03:58	0 00:01:35
Outgoing	5	0	0	0 00:02:06	0 00:01:39	0 00:00:25
Queue	18	0	0	0 00:32:54	0 00:03:00	0 00:01:50
Extension Averages:	9	0	106	0 00:14:19	N/A	0 00:01:32
Extension Totals:	28	0	317	0 00:42:57	0 00:03:58	N/A
804  User Name 2
Count of Type of Call	Number of Seconds	Type Of Call
Incoming	1	0	48	0 00:05:59	0 00:05:59	0 00:05:59
Outgoing	6	0	0	0 00:09:54	0 00:03:13	0 00:01:39
Queue	0	0	2	0 00:00:00	0 00:00:00	0 00:00:00
Extension Averages:	2	0	17	0 00:05:18	N/A	0 00:02:16
Extension Totals:	7	0	50	0 00:15:53	0 00:05:59	N/A
807  User Name 3
Count of Type of Call	Number of Seconds	Type Of Call
Incoming	11	0	257	0 01:00:04	0 00:11:13	0 00:05:28
Outgoing	41	0	5	0 00:55:04	0 00:19:07	0 00:01:21
Queue	45	0	0	0 01:41:38	0 00:07:41	0 00:02:16
Extension Averages:	32	0	87	0 01:12:15	N/A	0 00:02:14
Extension Totals:	97	0	262	0 03:36:46	0 00:19:07	N/A
808  User Name 4
Count of Type of Call	Number of Seconds	Type Of Call
Incoming	10	0	351	0 00:26:39	0 00:06:11	0 00:02:40
Outgoing	45	0	5	0 01:18:16	0 00:07:31	0 00:01:44
Queue	2	0	0	0 00:02:38	0 00:01:59	0 00:01:19
Extension Averages:	19	0	119	0 00:35:51	N/A	0 00:01:53
Extension Totals:	57	0	356	0 01:47:33	0 00:07:31	N/A

[v-kkf-msft]

Hi @SamBrown17 ,

Has your problem been solved? If it is solved, please mark a reply which is helpful to you.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[v-kkf-msft · Accepted solution]

Hi @SamBrown17 ,

I clean the data as follows:

1. Filter the rows that do not contain "Type" text and empty values in Column1.

2. Add a custom column, and then select Fill down to display the UserName.

3. Remove the row with the user's name in Column1.

The M code for the above steps is as follows:

let
    Source = Excel.Workbook(File.Contents("Excel Path"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each not Text.Contains([Column1], "Type")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "UserName", each if List.ContainsAny(Text.ToList(Text.End([Column1],1)),{"1","2","3","4","5","6","7","8","9"}) then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"UserName"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each not Text.Contains([Column1], "User Name"))
in
    #"Filtered Rows1"

And the final data is as follows, does this meet your expected output?

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

This can be done in a matrix visual, assuming that your source data is not too messy.  Can you show a sample of the source data?

[SamBrown17]

let
Source = Excel.Workbook(File.Contents("C:\Users\sambrown\Downloads\Weekly_Call_Type_Ext_Sum_20210802.xls"), null, true),
Sheet2 = Source{[Name="Sheet1"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}})
in
#"Changed Type"

[SamBrown17]

The data source is an excel spreadsheet the same as the table I sent above

[lbendlin]

That was my fear. That is a mess, not a data source. The effort to bring it into a useful structure in Power Query is too big.