Request Help on PQ Prob: De-concatenate Name data, Assoc w/YN data & Calc Percent Yes by Name

[Joe_Sauer]

In my Source query, I have concatenated Name data that is associated with separate YesNo data, that looks like the first table below. The actual set of names is a long list of ~100 people (in 'Last Name, First Name' format). Sometimes there is a single name in the Name cell; sometimes there are multiple names in the Name cell. There is no limit to the number of multiple names in the Name cell, but it is unlikely to ever be more than five. If there are multiple names in the Name cell, then the names are always separated by a semi-colon as shown in the table below.

YesNo        Name

Yes	Cat
No	Dog
Yes	Bat
Yes	Hog
No	Pig
Yes	Ox
No	Fox
Yes	Lamb
Yes	Cow
No	Ant
No	Cat;Dog
Yes	Bat;Hog
Yes	Pig;Ox
No	Fox;Lamb
No	Cow;Ant
Yes	Cat;Dog;Bat
Yes	Hog;Pig;Ox
No	Fox;Lamb;Cow
Yes	Lamb;Cow;Ant
Yes	Cat;Dog;Bat;Hog
No	Hog;Pig;Ox;Fox
Yes	Fox;Lamb;Cow;Ant
Yes	Cat;Bat;Pig
No	Dog;Hog;Ox
Yes	Bat;Pig;Fox
No	Hog;Ox;Lamb
Yes	Ant;Lamb;Ox
No	Cow;Fox;Pig
No	Dog;Bat
Yes	Hog;Pig
Yes	Ox;Fox
No	Lamb;Cow
Yes	Cow;Ant
No	Cat;Ant
Yes	Dog;Cow
Yes	Bat;Lamb
No	Hog;Fox
Yes	Pig;Ox

In PQ, I want to de-concatenate the name data, but continue to associate each de-concatenated name with that row's YN data. Then, calculate the total number of Yes's for each unique Name, and calculate the total number of entries for each unique Name. Then, calculate the percent of Yes's for each unique Name. I could probably do the last part as a 'Measure' in Power BI.

The final result of the above data would end up as follows:

Name   Yes's   Total  Pct_Yes

Cat	4	6	66.7%
Dog	3	7	42.9%
Bat	7	8	87.5%
Hog	5	9	55.6%
Pig	6	9	66.7%
Ox	6	9	66.7%
Fox	3	9	33.3%
Lamb	5	9	55.6%
Cow	5	9	55.6%
Ant	4	7	57.1%

Can anyone help me get started on this?? ... Thanks!

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZGxDsIwDET/JXP/4CYoQh0QZUVRhyIhxAAZQGo/n7ipY5sERsfOPd/Ze3e+vlzj2vHthsa7Y4jFLtyWIrW2aytV3dpbBk93PdjP0tmHWXUO4+OiyjZMMrl5KnJcAwUdnXmJUHyhkAFJJUxg2WyPdFF4wS818JLiAH91YZIRadgotH5FjIQ4Vb4FKfOOEgqps3RG9iqKNBoRiadNEpsWKUjVfMyJLbOSlPaVb2qNEsl+IkP2hsS2yfGlhg8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YesNo = _t, Name = _t]),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Name", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Name")
in
    #"Split Column by Delimiter"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[Joe_Sauer]

Thanks again lbendlin. I did integrate your solution and it worked 100% great. I don't understand exactly how it works, but it certainly did work. Thank you again for your very smart and quick help!

[Joe_Sauer]

Thank you lbendlin. I will check out your solution later today or tomorrow. I will reply on the results.  Thanks for taking the time to help me.