In Power Query populate a column based on whether a value exists in another column

Question

I am working in Excel Power Query. In the data an opportunity number (column A) will be listed several times; one row person assigned to helping on the opportunity. Another column (Column B) will indicate the name of each person and another (Column C) for the role of each assigned person. I want to create another column (Column D: All Roles) based on the following rule (Note this is a small sample but there are several other roles)I: If the role PA is assigned to an opportunity then populate PA for every line of that opportunity. If the role AA is assigned to an opportunity then populate AA for every line of that opportunity. If the role PA and the role AA are assigned to an opportunity then populate PA, AA for every line of that opportunity. Example

horseyride horseyride · Accepted Answer · 2021-05-07T14:45:38

Probably 500 ways to do this

Remove everything except the role and opportunity columns. Remove duplicates. Group on Opportunity, and edit the resulting group code in formula window so that it merges the roles into a single cell, so that it ends with:

each Text.Combine([Role],",")

Merge that back into original data

full sample code:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

// get list of unique roles for each Opportunity
#"Removed Columns" = Table.SelectColumns(Source,{"Opportunity", "Role"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
GroupEm = Table.Group(#"Removed Duplicates", {"Opportunity"}, {{"Concat", each Text.Combine(List.Sort([Role]),","), type text}}),

// merge into original data
#"Merged Queries" = Table.NestedJoin(Source,{"Opportunity"},GroupEm ,{"Opportunity"},"Table1",JoinKind.LeftOuter),
#"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Concat"}, {"All Roles"})
in #"Expanded Table1"

In Power Query populate a column based on whether a value exists in another column

1 Answers