Return values based on Yes or No responses where Pivot is not possible

[proreq]

Hello everyone,

I am trying to populate values based on the response 'No' from a survey results table. I am trying to get a report that gives me the name of the person first followed by question ID and the comment if he said No. Below is a table representation of what I am trying to achieve. I have tried all sorts of things and was just wondering if I am trying something that is not possible on Excel. Could anyone confirm the possibility of this please.

I have a table that looks like this

ID	Jack	Jack1	Jack2	Don	Don1	Don2	Abe	Abe1	Abe2
1​	Yes	NA		Yes	NA		No		Test comment
2​	Yes	NA		No		Test comment	No		Test comment
3​	Yes	NA		Yes	NA		Yes	NA
4​	Yes	NA		No		Test comment	Yes	NA
5​	Yes	NA		Yes	NA		No		Test comment

and I am trying to get a report that looks something like this

Jack
Don	2​	Don2
	4​	Don2
Abe	1​	Abe2
	2​	Abe2
	5​	Abe2

 

[sandy666]

is it acceptable?

ID	Custom.1	Custom.2	Custom.3	Custom.4
1	Abe	Abe2
2	Don	Don2	Abe	Abe2
4	Don	Don2
5	Abe	Abe2

 

[proreq]

Yes, thanks for your response.

Although, the below table would be perfect.

Clone of March 2020 BGRP

ID	Custom1	Custom2
1​	Abe	Abe2
2​	Don	Don2
2​	Abe	Abe2
4​	Don	Don2
5​	Abe	Abe2

 

[proreq]

Also, I was trying to populate the comments in the 3rd column. Below is an example.

ID	Custom1	Custom2
1​	Abe	Test comment (1 - Abe2)
2​	Don	Test comment (2 - Don2)
2​	Abe	Test comment (2 - Abe 2)
4​	Don	Test comment (4 - Don4)
5​	Abe	Test comment (5 - Abe2)

 

[sandy666]

with Power Query aka Get&Transform

ID	Jack	Jack1	Jack2	Don	Don1	Don2	Abe	Abe1	Abe2		ID	Custom.1	Custom.2
1	Yes	NA		Yes	NA		No		Test comment		1	Abe	Abe2
2	Yes	NA		No		Test comment	No		Test comment		2	Abe	Abe2
3	Yes	NA		Yes	NA		Yes	NA			2	Don	Don2
4	Yes	NA		No		Test comment	Yes	NA			4	Don	Don2
5	Yes	NA		Yes	NA		No		Test comment		5	Abe	Abe2

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value"),
    Filter = Table.SelectRows(Unpivot, each ([Value] = "No" or [Value] = "Test comment")),
    Group = Table.Group(Filter, {"ID"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Custom", each [Count][Attribute]),
    Extract = Table.TransformColumns(List, {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Len = Table.AddColumn(Extract, "Length", each Text.Length([Custom]), Int64.Type),
    Min = List.Min(Len[Length])+1,
    SplitPos = Table.ExpandListColumn(Table.TransformColumns(Len, {{"Custom", Splitter.SplitTextByRepeatedLengths(Min), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
    SortLen = Table.Sort(SplitPos,{{"Length", Order.Descending}}),
    Split = Table.SplitColumn(SortLen, "Custom", Splitter.SplitTextByAnyDelimiter({","}, QuoteStyle.Csv)),
    SortID = Table.Sort(Split,{{"ID", Order.Ascending}, {"Custom.1", Order.Ascending}}),
    TSC = Table.SelectColumns(SortID,{"ID", "Custom.1", "Custom.2"})
in
    TSC