Create conditional column in Power Query based on list value

Question

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.

Any help will be appreciated!

chillin · Accepted Answer · 2020-01-20 13:03:29Z

1

If your initial table looks something like:

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:

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

edited Jan 20, 2020 at 13:03

answered Jan 18, 2020 at 16:25

chillin

4,4861 gold badge11 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

torkestativ Over a year ago

Thanks! For it to solve my problem I would need it to return a string of text that I can define, and not a logical true/false. I only used "Yes"/"No" as an example, but in retrospect I understand that was a bad choice of example. For example I would like it to return "AB119" if it matches and "AD119" if it does not.

chillin Over a year ago

@torkestativ Have edited answer if you want to have another look. I wasn't able to test the code in the edit though.

MeissaMintaka · Accepted Answer · 2020-04-08 20:02:57Z

0

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.

answered Apr 8, 2020 at 20:02

MeissaMintaka

1

Collectives™ on Stack Overflow

Create conditional column in Power Query based on list value

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related