CODEWITHSUNDEEP
excelpowerquery
torkestativ
torkestativ

Reputation: 382

Create conditional column in Power Query based on list value

I want to create a conditional column in Excel Power Query that look up a list of companies and return a value like "Yes" if the company column in that given row corresponds with one of the companies in the list.

It should look something like this:

+----------+---------------------+-------+
| Company  |        List         | Match |
+----------+---------------------+-------+
| Apple    | Facebook, Instagram | No    |
| Facebook | Facebook, Instagram | Yes   |
+----------+---------------------+-------+

Constraint: I cannot create additional rows since the excel sheet will be used for importing information to a system, and therefore needs to be structured in a given way.

I have tried using the built in conditional column functionality in Power Query, but it can only look at a specific column, and not several. And it sees the companies list column as a big string like "Facebook, Instagram" and not separate values. Also, the company list is very long, so it would be very inefficient to create if-statements for each company.

What I have tried

Any help will be appreciated!

Upvotes: 0

Views: 3679

Answers (2)

MeissaMintaka
MeissaMintaka

Reputation: 1

Hi just to check I understand correctly - do you have a list of Apps and a known list of companies?

If so, you could try:

if
List.AnyTrue(List.Transform({
"Instagram",
"Facebook",
"WhatsApp"
}
,(substring) => Text.Contains([List],substring,Comparer.OrdinalIgnoreCase)))

then "Facebook"

else if

//then repeat for list of other company products 

else "No Company"

Though please note, this method is limited by order - i.e. if the [List] column contained both Apple and Facebook Apps/Products it would only designate it the first company listed in your query.

Upvotes: 0

chillin
chillin

Reputation: 4486

If your initial table looks something like:

Initial table

then this code:

let
    initialTable = Table.FromColumns({{"Apple", "Facebook"}, {"Facebook, Instagram", "Facebook, Instagram"}}, type table [Company = text, List = text]),
    matchColumn = Table.AddColumn(initialTable, "Match", each List.Contains(Text.Split([List], ", "), [Company]), type logical)
in
    matchColumn

should give expected output:

Output table

  • I split the list of companies on the delimiter , (there is a space after the comma), since that is what you've shown in your question. You can change the code to use , if there is no space.
  • I've used true/false (instead of "YES"/"NO").
  • If any of the company names contain commas (i.e. commas that should be interpreted literally and not as delimiters), then you may get unexpected behaviour/output. Splitter.SplitTextByDelimiter can help to return a better splitting function, but it requires your values to already be quoted (which they don't appear to be).

Alternatively, try:

let
    initialTable = Table.FromColumns({{"Apple", "Facebook"}, {"Facebook, Instagram", "Facebook, Instagram"}}, type table [Company = text, List = text]),
    matchColumn = Table.AddColumn(initialTable, "Match", each if List.Contains(Text.Split([List], ", "), [Company]) then "AB119" else "AD119", type text)
in
    matchColumn

Upvotes: 1

Related Questions