Mutiple columns split by delimiter, best set up for % in Power Query (for set up in Power BI)

[Anonymous]

Looking to find % item chosen as most or least helpful -- count most helpful/Item list occurance. Original form has a select all that apply and different item lists depending on the project. Data set looks something like this:

Entry ID	Respondant	Client ID	Project ID	Most Helpful	Least Helpful	Item List (they had to choose from)
1	1	Client A	AAA	Handbook,Poster,Factsheet	Letter	Handbook,Poster,Factsheet,Letter,PPT
2	2	Client A	AAA	Poster,Factsheet	Handbook	Handbook,Poster,Factsheet,Letter,PPT
3	3	Client A	AAA	Handbook,Poster	PPT,Letter	Handbook,Poster,Factsheet,Letter,PPT
4	1	Client B	BBB	Handbook	PPT	Poster,Flyer,Handbook,PPT
5	1	Client C	CCC	Factsheet	Letter	Factsheet,Letter,PPT
6	2	Client C	CCC	Factsheet	PPT	Factsheet,Letter,PPT

How would you recommend setting up a query in order to acheive the following data set (and to be filterable by Client/Project ID if possible?):

Item name	(Count) Most Helpful	(Count) Least Helpful	# times listed as an option	% Most helpful	% Least Helpful
Letter	0	3	5	0%	60%
Factsheet	4	0	5	80%	0%
PPT	0	3	6	0%	50%
Handbook	3	1	4	75%	25%
Poster	3	0	4	75%	0%
Flyer	0	0	1	0%	0%

I've used split column by delimiter and set up measures to find disctinct count by entry ID for count of most and least helpful, but not sure where to go from there - I'm sure there's a better way to set it up for finding the percentages easily. Any suggestions greatly appreciated! Thank you!

[lbendlin · Accepted solution]

Your item list is immutable for each entry. So expand that to rows  

Then mark the helpfulness, either in the existing columns or in a new column.

After that the measures will be easy.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWPnnMzUvBIFRyDT0RFEeiTmpSTl52frBOQXl6QW6bglJpcUZ6SmlgDlfFJLgEL4FOlAlOgEBIQoxepEKxkBFRthswaL6TBDSTHfGKjYmAhvgGwMCNEh3QMmqOHkBGQ6OTmhuhakFuGjnEogiTAfao4pqjnOIKYziMQavjidY4YantiNgbgHuxmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Entry ID" = _t, Respondant = _t, #"Client ID" = _t, #"Project ID" = _t, #"Most Helpful" = _t, #"Least Helpful" = _t, #"Item List" = _t]),
    #"Split Column by Delimiter2" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Item List", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Item List"),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter2",each [Most Helpful],each if List.Contains(Text.Split([Most Helpful],","),[Item List]) then 1 else null,Replacer.ReplaceValue,{"Most Helpful"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each [Least Helpful],each if List.Contains(Text.Split([Least Helpful],","),[Item List]) then 1 else null,Replacer.ReplaceValue,{"Least Helpful"})
in
    #"Replaced Value1"

[Anonymous]

Thank you so much!! To confirm I understand, in order to get the total count per item for most helpful (across all clients/projects), would you set up a measure for each unique/distinct item in item list and a condition for when there is a 1 in most helpful or least helpful column?

[lbendlin]

You can do that, or you can use explicit measures too.