Pivot Table to Report Confirmation (or not) Status

[Greg Stough]

Hey Everyone, thanks for looking at this post. Hopefully the details provide a clear description of what I need.

Each month I create a spreadsheet report that provides details on responses from about 2000 people to indicate whether or not they confirmed receipt of a monthly system generated message. This report is then sent to a leadership team for review. The report is a pivot table with Department #, ID, Name and if they confirmed message receipt or not. This report also includes some slicers, charts and basic statistical information. I created the report a few years ago, and are now revisiting it to see if I can find a simpler way to indicate the confirmation status, and to provide some additional detail. I suspect the solution is somewhat simple, but I've been working with the current report for so long I'm not able to see alternatives.

My challenge is that notifications are sent to each person through various channels (phone, email, SMS, etc.). The raw data I receive includes multiple rows for each person - one row for each channel the message was sent. If someone confirms receipt through SMS, but they also received an email and a phone call, the raw data has three rows of data for that one person. Only one row will ever show that they confirmed, the other two rows will show they did not confirm through those channels. My goal is to display each person's name (and some other data) on a single row and in the last column indicate if they are Confirmed or Unconfirmed.

My current pivot table solution (tabular layout) has Department, ID, Name in rows and Confirmation Response (Y or N) in columns. The Confirmation Response field is added to the Values field in the PT and counts how many times they did, or did not confirm. They can confirm only once, but each communications method that is not confirmed is also counted, as unconfirmed. Conditional formatting changes the row for each unconfirmed person RED, so they are easily identified from those that did confirm (black text). When someone does not confirm at all, the Y cell for that record is blank, and triggers the conditional formatting. The Y and N columns are hidden, to keep the report less cluttered for viewers.

I've attached a two screen shots to provide visuals. The RAW DATA screenshot shows a small sample of the data I receive. The REPORT screenshot shows a mocked up sample of the Current Report, as well as the Desired Report

 

[sandy666]

you said "in single row" and no "unconfirmed", so?

 

[Greg Stough]

or this

Dept	ID	Name	Confirmed
3878	4078, 27699	WILLIAM, THOMAS	Confirmed
3932	30442, 71840, 72565	JAMES, BAYARD, CARL	Confirmed
3850	69421	MATTHEW	Confirmed
4170	71729	WILLIAM	Confirmed

Having IDs and names in the same cell won't work either. The real data has first and last names. Some departments have a dozen or so members. and others may have over 100 members. To keep things simple, I've also left out a column that provides any text messages included with the response.

 

[Greg Stough]

you said "in single row" and no "unconfirmed", so?

To clarify, no "unconfirmed" connected so someone that has actually confirmed one response. From the report perspective, each person either confirmed or they are unconfirmed. I don't want the report to say John smith is confirmed, and directly under that row is a row that is entirely empty except for Unconfirmed (because John smith didn't confirm every message he received. Message are sent through phone, email, SMS. One message is sent than a few minutes later a second message is sent if the previous one was not confirmed. So many people that confirm, will also have some messages that they did not confirm. I want to display when someone confirms, but I don't want to display uncofirmed for the messages that they were unable to respond to.

 

[alansidman]

Does this look correct to you?

Book5
	A	B	C	D
1	DATA.Dept	DATA.Name	ID	Confirmed
2	3850	MATTHEW	69421	Confirmed
3	3878	GLENN	27830	Not Confirmed
4	3878	THOMAS	27699	Confirmed
5	3878	WILLIAM	4078	Confirmed
6	3932	BAYARD	71840	Confirmed
7	3932	CARL	72565	Confirmed
8	3932	JAMES	30442	Confirmed
9	4170	RAPHAEL	72733	Not Confirmed
10	4170	WILLIAM	71729	Confirmed
Sheet3

Here is the Mcode from Power Query if you concur with the solution.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dept", Int64.Type}, {"ID", Int64.Type}, {"Name", type text}, {"Call Result", type text}, {"Attempt", Int64.Type}, {"Delivery Method", type text}, {"Confirmed?", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"DATA", each _, type table [Dept=number, ID=number, Name=text, Call Result=text, Attempt=number, Delivery Method=text, #"Confirmed?"=text]}}),
    #"Expanded DATA" = Table.ExpandTableColumn(#"Grouped Rows", "DATA", {"Dept", "Name", "Confirmed?"}, {"DATA.Dept", "DATA.Name", "DATA.Confirmed?"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded DATA", {{"ID", type text}}, "en-US"),{"ID", "DATA.Confirmed?"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Removed Duplicates" = Table.Distinct(#"Merged Columns", {"Merged"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Duplicates", "Merged", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Merged.1", "Merged.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", Int64.Type}, {"Merged.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Merged.1", "ID"}, {"Merged.2", "Confirm"}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Confirm]), "Confirm", "ID"),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "ID", each if[Y]<> null then [Y] else [N]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Confirmed", each if [Y] <> null then "Confirmed" else "Not Confirmed"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Y", "N"})
in
    #"Removed Columns"

 

[Greg Stough]

Does this look correct to you?

Book5
	A	B	C	D
1	DATA.Dept	DATA.Name	ID	Confirmed
2	3850	MATTHEW	69421	Confirmed
3	3878	GLENN	27830	Not Confirmed
4	3878	THOMAS	27699	Confirmed
5	3878	WILLIAM	4078	Confirmed
6	3932	BAYARD	71840	Confirmed
7	3932	CARL	72565	Confirmed
8	3932	JAMES	30442	Confirmed
9	4170	RAPHAEL	72733	Not Confirmed
10	4170	WILLIAM	71729	Confirmed
Sheet3

Here is the Mcode from Power Query if you concur with the solution.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dept", Int64.Type}, {"ID", Int64.Type}, {"Name", type text}, {"Call Result", type text}, {"Attempt", Int64.Type}, {"Delivery Method", type text}, {"Confirmed?", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"DATA", each _, type table [Dept=number, ID=number, Name=text, Call Result=text, Attempt=number, Delivery Method=text, #"Confirmed?"=text]}}),
    #"Expanded DATA" = Table.ExpandTableColumn(#"Grouped Rows", "DATA", {"Dept", "Name", "Confirmed?"}, {"DATA.Dept", "DATA.Name", "DATA.Confirmed?"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded DATA", {{"ID", type text}}, "en-US"),{"ID", "DATA.Confirmed?"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Removed Duplicates" = Table.Distinct(#"Merged Columns", {"Merged"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Duplicates", "Merged", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Merged.1", "Merged.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", Int64.Type}, {"Merged.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Merged.1", "ID"}, {"Merged.2", "Confirm"}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Confirm]), "Confirm", "ID"),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "ID", each if[Y]<> null then [Y] else [N]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Confirmed", each if [Y] <> null then "Confirmed" else "Not Confirmed"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Y", "N"})
in
    #"Removed Columns"

Yes that looks good! The should be before the name, but otherwise that looks good. I've recently begun working with Power Query, and are not too familiar with Mcode. But I believe it can review the code to figure out how to adapt it to what I have. I'll work with this a bit over the next day or so to see how it works.

Thank you for your help!

 

[alansidman]

Moved the column.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dept", Int64.Type}, {"ID", Int64.Type}, {"Name", type text}, {"Call Result", type text}, {"Attempt", Int64.Type}, {"Delivery Method", type text}, {"Confirmed?", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"DATA", each _, type table [Dept=number, ID=number, Name=text, Call Result=text, Attempt=number, Delivery Method=text, #"Confirmed?"=text]}}),
    #"Expanded DATA" = Table.ExpandTableColumn(#"Grouped Rows", "DATA", {"Dept", "Name", "Confirmed?"}, {"DATA.Dept", "DATA.Name", "DATA.Confirmed?"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded DATA", {{"ID", type text}}, "en-US"),{"ID", "DATA.Confirmed?"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Removed Duplicates" = Table.Distinct(#"Merged Columns", {"Merged"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Duplicates", "Merged", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Merged.1", "Merged.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", Int64.Type}, {"Merged.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Merged.1", "ID"}, {"Merged.2", "Confirm"}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Confirm]), "Confirm", "ID"),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "ID", each if[Y]<> null then [Y] else [N]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Confirmed", each if [Y] <> null then "Confirmed" else "Not Confirmed"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Y", "N"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"DATA.Dept", "ID", "DATA.Name", "Confirmed"})
in
    #"Reordered Columns"

Book51.xlsx
	A	B	C	D
1	DATA.Dept	ID	DATA.Name	Confirmed
2	3850	69421	MATTHEW	Confirmed
3	3878	27830	GLENN	Not Confirmed
4	3878	27699	THOMAS	Confirmed
5	3878	4078	WILLIAM	Confirmed
6	3932	71840	BAYARD	Confirmed
7	3932	72565	CARL	Confirmed
8	3932	30442	JAMES	Confirmed
9	4170	72733	RAPHAEL	Not Confirmed
10	4170	71729	WILLIAM	Confirmed
Sheet2

 

[alansidman]

Thanks for the feedback.

 

[Fazza]

some other ways are a query (table) or a basic pivot table (don't need power query) with dataset defined by

SELECT DISTINCT A.Dept, A.ID, A.Name, 'Confirmed' AS [Confirmed]
FROM Data A
WHERE A.[Confirmed] = 'Y'
UNION
SELECT DISTINCT B.Dept, B.ID, B.Name, 'Unconfirmed'
FROM Data B
WHERE B.ID NOT IN (
SELECT DISTINCT C.ID
FROM Data C
WHERE C.[Confirmed] = 'Y')

PS. I started with renaming the [Confirmed?] field to [Confirmed] as the "?" was a problem for the query.

 

[Greg Stough]

some other ways are a query (table) or a basic pivot table (don't need power query) with dataset defined by

SELECT DISTINCT A.Dept, A.ID, A.Name, 'Confirmed' AS [Confirmed]
FROM Data A
WHERE A.[Confirmed] = 'Y'
UNION
SELECT DISTINCT B.Dept, B.ID, B.Name, 'Unconfirmed'
FROM Data B
WHERE B.ID NOT IN (
SELECT DISTINCT C.ID
FROM Data C
WHERE C.[Confirmed] = 'Y')

PS. I started with renaming the [Confirmed?] field to [Confirmed] as the "?" was a problem for the query.

Thank you Fazza for the idea. I haven't yet had time to try it but I will to see how it works. Thanks!