Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
SamBrown17
Helper I
Helper I

Remodel an excel report to suit Power Bi

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 CallAnswered
Count
Abandoned Before Minimum
Count
Abandoned After Minimum
Count
Talk Time TotalTalk Time MaximumTalk Time Average
222  User Name 1      
Count of Type of CallNumber Type Of Call    
Incoming503170 00:07:570 00:03:580 00:01:35
Outgoing5000 00:02:060 00:01:390 00:00:25
Queue18000 00:32:540 00:03:000 00:01:50
Extension Averages:901060 00:14:19N/A0 00:01:32
Extension Totals:2803170 00:42:570 00:03:58N/A
804  User Name 2      
Count of Type of CallNumber of SecondsType Of Call    
Incoming10480 00:05:590 00:05:590 00:05:59
Outgoing6000 00:09:540 00:03:130 00:01:39
Queue0020 00:00:000 00:00:000 00:00:00
Extension Averages:20170 00:05:18N/A0 00:02:16
Extension Totals:70500 00:15:530 00:05:59N/A
807  User Name 3      
Count of Type of CallNumber of SecondsType Of Call    
Incoming1102570 01:00:040 00:11:130 00:05:28
Outgoing41050 00:55:040 00:19:070 00:01:21
Queue45000 01:41:380 00:07:410 00:02:16
Extension Averages:320870 01:12:15N/A0 00:02:14
Extension Totals:9702620 03:36:460 00:19:07N/A
808  User Name 4      
Count of Type of CallNumber of SecondsType Of Call    
Incoming1003510 00:26:390 00:06:110 00:02:40
Outgoing45050 01:18:160 00:07:310 00:01:44
Queue2000 00:02:380 00:01:590 00:01:19
Extension Averages:1901190 00:35:51N/A0 00:01:53
Extension Totals:5703560 01:47:330 00:07:31N/A
1 ACCEPTED SOLUTION
v-kkf-msft
Community Support
Community Support

Hi @SamBrown17 ,

 

I clean the data as follows:


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

 

image.png

 

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

image.png

image.png

 

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

 

image.png

 

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?

 

vkkfmsft_0-1628228917649.png

 

 

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.

View solution in original post

6 REPLIES 6
v-kkf-msft
Community Support
Community Support

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
Community Support
Community Support

Hi @SamBrown17 ,

 

I clean the data as follows:


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

 

image.png

 

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

image.png

image.png

 

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

 

image.png

 

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?

 

vkkfmsft_0-1628228917649.png

 

 

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
Super User
Super User

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?

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"

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

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.