Pull Individual Data from Table

Hi,

From the attached data I want to pull and sum Calls Offered (highlighted in red), Calls Answered, Max Abandoned & Abandoned Time.

The issue is there is text also in column B (B6) & C (C4), so if I place in pivot table I don't get the accurate figures.
I have tried with IF statement in the column but didn't work.
Also the other column won't pull in a pivot table either as there is no unique reference in Column A (so it could be numbers from any cell in the column)

Is there a way I can pull this information (Even on MS Access if necessary)? The report is downloaded in this format for some reason (Avaya)

you mean something like in black table?

time column is in duration format: d.hh.mm.ss

Originally Posted by sandy666

you mean something like in black table?

time column is in duration format: d.hh.mm.ss

Almost! How did you do that??

I have highlighted in yellow in sheet1 that are 2 separate groups, so I want to split the stats.

In Sheet2, you can see how it fails in a pivot table

Originally Posted by manny88

Almost! How did you do that??

I have highlighted in yellow in sheet1 that are 2 separate groups, so I want to split the stats.

In Sheet2, you can see how it fails in a pivot table

I'm looking for the data to sit under the skill set names eg. "C_Queue_Admin - 1181" But it's difficult because on the report that title is in the Calls Accepted column for some reason!

Like this one?

btw. if you want to see how it works you'll need PowerQuery add-in for Ex2010 Pro Plus from MS site

I'm not able to add PQ on my current set up at home. I can check at work which is MS 2016

Can do a quick explanation please?

sure, steps are here:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Skill ID] <> null)),
    #"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows", "Calls answered", "Calls answered - Copy"),
    #"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column",{"Skill ID", "Calls answered - Copy", "Calls Offered", "Calls answered", "Max Abandoned", "Abandoned Time"}),
    #"Added Conditional Column" = Table.AddColumn(#"Reordered Columns", "Custom", each if [#"Calls answered - Copy"] = "C_Queue_Admin - 1181" then "C_Queue_Admin - 1181" else null ),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if [#"Calls answered - Copy"] = "C_CS_Relations - 1185" then "C_CS_Relations - 1185" else null ),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"Custom", type text}, {"Custom.1", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Custom", "Custom.1"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Replaced Value" = Table.ReplaceValue(#"Merged Columns","",null,Replacer.ReplaceValue,{"Merged"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Merged"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Skill ID", "Merged"}, {{"CO", each List.Sum([Calls Offered]), type anynonnull}, {"CA", each List.Sum([Calls answered]), type anynonnull}, {"MA", each List.Sum([Max Abandoned]), type number}, {"AT", each List.Sum([Abandoned Time]), type number}}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Grouped Rows", {"CO", "CA", "MA", "AT"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Errors",{{"MA", Int64.Type}, {"AT", type duration}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Merged", "C_"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"MA", Int64.Type}})
in
    #"Changed Type2"

Originally Posted by sandy666

sure, steps are here:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Skill ID] <> null)),
    #"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows", "Calls answered", "Calls answered - Copy"),
    #"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column",{"Skill ID", "Calls answered - Copy", "Calls Offered", "Calls answered", "Max Abandoned", "Abandoned Time"}),
    #"Added Conditional Column" = Table.AddColumn(#"Reordered Columns", "Custom", each if [#"Calls answered - Copy"] = "C_Queue_Admin - 1181" then "C_Queue_Admin - 1181" else null ),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if [#"Calls answered - Copy"] = "C_CS_Relations - 1185" then "C_CS_Relations - 1185" else null ),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"Custom", type text}, {"Custom.1", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Custom", "Custom.1"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Replaced Value" = Table.ReplaceValue(#"Merged Columns","",null,Replacer.ReplaceValue,{"Merged"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Merged"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Skill ID", "Merged"}, {{"CO", each List.Sum([Calls Offered]), type anynonnull}, {"CA", each List.Sum([Calls answered]), type anynonnull}, {"MA", each List.Sum([Max Abandoned]), type number}, {"AT", each List.Sum([Abandoned Time]), type number}}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Grouped Rows", {"CO", "CA", "MA", "AT"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Errors",{{"MA", Int64.Type}, {"AT", type duration}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Merged", "C_"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"MA", Int64.Type}})
in
    #"Changed Type2"

Wow! What language is that?

I will check it out on PQ at work tomorrow and see what it says

Originally Posted by sandy666

Don't quote whole post, please?

I showed you M language because without PowerQuery you will see nothing, to see how it was done you "must have" PQ installed or built-in.

Will be better to read:

• Getting Started with Get & Transform in Excel 2016
• Microsoft Power Query for Excel Help

This is what I entered

let

    Source = Excel.Workbook(File.Contents("C:\Users\MAhmed\Downloads\Book1.xlsx"), null, true),

    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}}),

    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Skill ID"}}),

    #"Promoted Headers" = Table.PromoteHeaders(#"Renamed Columns"),

    #"Renamed Columns1" = Table.RenameColumns(#"Promoted Headers",{{"Column1", "Skill ID"}}),

    #"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns1", "CallsOffered", "CallsOffered - Copy"),

    #"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column",{"Skill ID", "CallsOffered - Copy", "CallsOffered", "Calls Answered", "Calls Answered After Threshold", "%Calls Answered After Threshold", "Calls Answered Delay", "Average Calls Answered Delay", "Max Answered Delay", "MaxSkillsetAbandonedDelay", "SkillsetAbandoned", "SkillsetAbandonedDelay", "Timestamp", "WaitTime", "TalkTime"}),

    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"CallsOffered - Copy", type text}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if [#"CallsOffered - Copy"] = "CRYS_AFS_Admin - 12039" then "CRYS_AFS_Admin - 12039" else null),

    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [#"CallsOffered - Copy"] = "CRYS_Customer_Relations - 12038" then "CRYS_Customer_Relations -12038" else null),

    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each if [#"CallsOffered - Copy"] = "CRYS_Customer_Service - 12037" then "CRYS_Customer_Service - 12037" else null),

    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each if [#"CallsOffered - Copy"] = "CRYS_D_AFS_Adaptive - 12028" then "CRYS_D_Ski_AFS_Adaptive - 12028" else null),

    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Custom.4", each if [#"CallsOffered - Copy"] = "CRYS_D_AFS_EU - 12008" then "CRYS_D_Ski_AFS_EU - 12008" else null),

    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Custom.5", each if [#"CallsOffered - Copy"] = "CRYS_D_AFS_Group_EU - 12024" then "CRYS_D_AFS_Group_EU - 12024" else null),

    #"Added Custom6" = Table.AddColumn(#"Added Custom5", "Custom.6", each if [#"CallsOffered - Copy"] = "CRYS_D_Sales_Adaptive - 12018" then "CRYS_D_Sales_Adaptive - 12018" else null),

    #"Added Custom7" = Table.AddColumn(#"Added Custom6", "Custom.7", each if [#"CallsOffered - Copy"] = "CRYS_D_Sales_EU - 11995" then "CRYS_D_Sales_EU - 11995" else null),

    #"Added Custom8" = Table.AddColumn(#"Added Custom7", "Custom.8", each if [#"CallsOffered - Copy"] = "CRYS_D_Sales_Group_EU - 12014" then "CRYS_D_Sales_Group_EU - 12014" else null),

    #"Added Custom9" = Table.AddColumn(#"Added Custom8", "Custom.9", each if [#"CallsOffered - Copy"] = "CRYS_D_Sales_Web_Err - 11996" then "CRYS_D_Sales_Web_Err - 11996" else null),

    #"Added Custom10" = Table.AddColumn(#"Added Custom9", "Custom.10", each if [#"CallsOffered - Copy"] = "CRYS_New_Credit_Control_Skill - 12035" then "CRYS_New_Credit_Control_Skill - 12035" else null),

    #"Added Custom11" = Table.AddColumn(#"Added Custom10", "Custom.11", each if [#"CallsOffered - Copy"] = "CRYS_Credit_Control - 12034" then "CRYS_Credit_Control - 12034" else null),

    #"Added Custom12" = Table.AddColumn(#"Added Custom11", "Custom.12", each if [#"CallsOffered - Copy"] = "CRYS_Incident_Line - 12036" then "CRYS_Incident_Line - 12036" else null),

    #"Added Custom13" = Table.AddColumn(#"Added Custom12", "Custom.13", each if [#"CallsOffered - Copy"] = "CRYS_Fest - 12001" then "CRYS_Fest - 12001" else null),

    #"Added Custom14" = Table.AddColumn(#"Added Custom13", "Custom.14", each if [#"CallsOffered - Copy"] = "CRYS_Travelzoo - 12002" then "CRYS_Travelzoo - 12002" else null),

    #"Added Custom15" = Table.AddColumn(#"Added Custom14", "Custom.15", each if [#"CallsOffered - Copy"] = "CRYS_Ski_AFS_Adaptive - 12033" then "CRYS_T_AFS_Adaptive - 12033" else null),

    #"Added Custom16" = Table.AddColumn(#"Added Custom15", "Custom.16", each if [#"CallsOffered - Copy"] = "CRYS_T_AFS_EU - 12011" then "CRYS_T_AFS_EU - 12011" else null),

    #"Added Custom17" = Table.AddColumn(#"Added Custom16", "Custom.17", each if [#"CallsOffered - Copy"] = "CRYS_T_AFS_Group_EU - 12029" then "CRYS_T_AFS_Group_EU - 12029" else null),

    #"Added Custom18" = Table.AddColumn(#"Added Custom17", "Custom.18", each if [#"CallsOffered - Copy"] = "CRYS_T_Sales_Adaptive - 12023" then "CRYS_T_Sales_Adaptive - 12023" else null),

    #"Added Custom19" = Table.AddColumn(#"Added Custom18", "Custom.19", each if [#"CallsOffered - Copy"] = "CRYS_T_Sales_EU - 12003" then "CRYS_T_Sales_EU - 12003" else null),

    #"Added Custom20" = Table.AddColumn(#"Added Custom19", "Custom.20", each if [#"CallsOffered - Copy"] = "CRYS_T_Sales_Group_EU - 12019" then "CRYS_T_Sales_Group_EU - 12019" else null),

    #"Added Custom21" = Table.AddColumn(#"Added Custom20", "Custom.21", each if [#"CallsOffered - Copy"] = "CRYS_T_Sales_Web_Errr - 12004" then "CRYS_T_Sales_Web_Errr - 12004" else null),

    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom21",{{"Custom", type text}, {"Custom.1", type text}, {"Custom.2", type text}, {"Custom.3", type text}, {"Custom.4", type text}, {"Custom.5", type text}, {"Custom.6", type text}, {"Custom.7", type text}, {"Custom.8", type text}, {"Custom.9", type text}, {"Custom.10", type text}, {"Custom.11", type text}, {"Custom.12", type text}, {"Custom.13", type text}, {"Custom.14", type text}, {"Custom.15", type text}, {"Custom.16", type text}, {"Custom.17", type text}, {"Custom.18", type text}, {"Custom.19", type text}, {"Custom.20", type text}, {"Custom.21", type text}}),

    #"Merged Columns" = Table.CombineColumns(#"Changed Type2",{"Custom", "Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7", "Custom.8", "Custom.9", "Custom.10", "Custom.11", "Custom.12", "Custom.13", "Custom.14", "Custom.15", "Custom.16", "Custom.17", "Custom.18", "Custom.19", "Custom.20", "Custom.21"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),

    #"Replaced Value" = Table.ReplaceValue(#"Merged Columns","",null,Replacer.ReplaceValue,{"Merged"}),

    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Merged"}),

    #"Grouped Rows" = Table.Group(#"Filled Down", {"Skill ID", "Merged"}, {{"CallsOffered", each List.Sum([CallsOffered]), type anynonnull}, {"CallsAccepted", each List.Sum([Calls Answered]), type number}, {"CallsAbandoned", each List.Sum([SkillsetAbandoned]), type anynonnull}, {"AvgWaitTime", each List.Average([WaitTime]), type number}, {"AvgTalkTime", each List.Sum([TalkTime]), type number}}),

    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Grouped Rows", {"CallsOffered"}),

    #"Filtered Rows" = Table.SelectRows(#"Removed Errors", each true)

in

    #"Filtered Rows"

But the table doesn't come out like yours. It looks like this instead;
image001 (1).png

I entered data for 21st Feb and 22nd Deb in a dump file so the I can refresh the PQ in a seperate document. The idea was for the PQ to populate each days data under the previous days data. I could then add this into tables/graphs. But instead it is doubling the figures into 21st Feb data, whilst also adding in for 22nd Feb (in a different format)

Don't quote whole post in answer - use Reply instead of Reply With Quote

I'll read your post a little later

You are doing quite different than I showed you. So I still don't know what are you tryin' to achieve

I have attached Book1 which contains the actual raw data set. I have added comments to relevant cells and highlighted 2 types of cells.

Cells in column A.
"00:00" in Column A contains Total Calls Offered in Column B.
Date in Column A to reference each day's data.
Skillset ID contains Group name in Column B.

Outcome required is a table as follows;
Date > Group Name (Skillset ID) > "00:00" Calls Offered Totals


The date is needed so I can keep pasting data every day and refresh PQ, which should populate each days data Separately because I experienced the data multiplying in previous day data each time.

The PQ Work doc is just to show what I tried so far

I get the premise of this route way now, thank you for your help!

But I will close this thread because a different report has now been made available in a standardised table where I can pull data as required.

no problem

have a nice day