Expand all | Collapse all sort by thread Construct a column based on values in two other columns using mCODE in Power Query

Construct a column based on values in two other columns using mCODE in Power QueryJump to Best AnswerFollow
Rocking Mark
Rocking MarkApr 18, 2023 07:02 AM
Hope somebody can help me. I need some mCODE for Power BI to help construct a column based on two other …
1. Construct a column based on values in two other columns using mCODE in Power Query

Rocking Mark
Posted Apr 18, 2023 07:02 AM
Hope somebody can help me. I need some mCODE for Power BI to help construct a column based on two other columns in a data table. People were asked about eating Peas. They answered in different ways, some answers were text strings without spaces. I have the data in a table which details Names and the Answer returned by that name. I would like mCODE to constuct a column that gives an Answer “YES” if anyone of the same name in the table gives an answer that contains PEAS in the answer string, regardless of string case or string spacing. The mCODE should construct a column called “Yes or No”. The code should not rely on rows being listed in name or answer order. I give a sample of my massive data table below. One Fred answered “I like Peas” so all FREDS get assigned “YES”. No one called Anne Mentioned PEAS so all Annes Get assigned “No”. George’s Answer had no spaces but contained Peas so All Georges would get assigned Yes. Hope this Makes sense and Somebody can code this. Would Take a Dax Code Solution, but would prefer mCODE Thanks in Advance rockingmark

Name ANSWER Peas Mentioned
Yes or No
Fred I like Peas YES
Fred Beans YES
Fred Don’t Like Toast YES
Anne Like Bread No
Anne Chocolate No
Mark BEANS YES
Mark don’t Like Peas YES
TIM Choclate Yes YES
TIM Tea YES
TIM Coffees No YES
TIM PEAS YES
TIM Pizza horrible YES
TIM Chips Yummy YES
Sarah Yuk Mash NO
Sarah Salty Corn NO
Sarah Popcorn NO
Sarah Carrots Nice NO
Sarah dinner NO
Bert PeAS YES
Angie Sugar NO
Angie Cars NO
Angie I don’t eat breakfast NO
GEORGE YESIEATPEASDONTYOU YES

——————————
Rocking Mark
——————————

2. RE: Construct a column based on values in two other columns using mCODE in Power Query Best Answer

Bronze Contributor
James Watts
Posted Apr 19, 2023 04:40 AM
Load the data.
Create a column that checks if the text converted to uppercase contains “PEAS”, and set it to 1 if true, 0 if false
Group the data on Name, and have a column that is the sum of this custom column
Create a column that is “Yes” if the sum is 1, “No” if 0.
Expand the retained Answer column.

let
Source =……
#”Changed Type” = Table.TransformColumnTypes(Source,{{“Name”, type text}, {“ANSWER”, type text}}),
#”Added Custom” = Table.AddColumn(#”Changed Type”, “HasPeas”, each if Text.Contains(Text.Upper([ANSWER]),”PEAS”) then 1 else 0),
#”Grouped Rows” = Table.Group(#”Added Custom”, {“Name”}, {{“Peas”, each List.Sum([HasPeas]), type number}, {“All Others”, each _, type table [Name=nullable text, ANSWER=nullable text, HasPeas=number]}}),
#”Added Custom1″ = Table.AddColumn(#”Grouped Rows”, “Peas Mentioned”, each if [Peas] > 0 then “Yes” else “No”),
#”Removed Columns” = Table.RemoveColumns(#”Added Custom1″,{“Peas”}),
#”Expanded All Others” = Table.ExpandTableColumn(#”Removed Columns”, “All Others”, {“ANSWER”}, {“ANSWER”})
in
#”Expanded All Others”

——————————
James Watts
Business Intelligence Analyst
——————————

3. RE: Construct a column based on values in two other columns using mCODE in Power Query

Rocking Mark
Posted Apr 19, 2023 05:27 AM
James, Thankyou so much for your time, worked first time in exactly the way I wanted. The summary of how to do it before the code is really useful. Helps us novices learn! Cheers, Mark.

——————————
Rocking Mark
——————————