CODEWITHSUNDEEP
excelpowerquerym
Bjorn Morrhaye
Bjorn Morrhaye

Reputation: 731

Conditional formatting based on lists

I'm new to M and would like to create an "if, then, else statement" based on values inside a list.

Basically I have 4 lists:

let
    FoodCompanies = {"Nestlé", "Pepsico", "Unilever"},
    ClothingCompanies = {"Nike", "Ralph Lauren", "Old Navy"},
    TechCompanies = {"Apple", "Samsung", "IBM"},
    AllCompanies = {FoodCompanies,ClothingCompanies,TechCompanies}

Now I want to create a conditional column that checks for another column (tag) if one of the values is present and based on that makes a calculation.

| ItemId| DateOfSale | tag                     | 
| 001   | 01/01/1980 | Nestlé                  |
| 002   | 01/01/1980 | Nike, Apple             |
| 003   | 01/01/1980 | Unilever, Old Navy, IBM |
| 004   | 01/01/1980 | Samsung                 |

So ... I start like this:

#"Added Conditional Column" = Table.AddColumn(#"Renamed Columns3", "type", each

Single values

if [tag] = "" then "Empty tag" 
else if [tag] = "Nestlé" then "Nestlé"
else if [tag] = "Nike" then "Nike"
...

Multiple values

It's for the multiple values I don't know how to create the logic

If tag contains more then 1 value from FoodCompanies but not from ClosthingCompanies or Techcompanies I want it to be "FoodCompanies"

If tag contains more then 1 value from ClothingCompanies but not from FoodCompanies or Techcompanies I want it to be "ClothingCompanies"

If tag contains 2 values from AllCompanies it should be "MixedCompanies"

if tag contains all values from AllCompanies it should be "AllofThem"

...

Anyone can help me on the way? I would do it like 

else if List.Count(FoodCompanies) > 1 and ( List.Count(ClothingCompanies) < 1 or List.Count(Techcompanies) < 1)  then "FoodCompanies"

but how do I evaluate it against the tag value?

Upvotes: 0

Views: 189

Answers (1)

Olly
Olly

Reputation: 7891

Here's one approach, which converts your list of companies to a table, matches the tag values, filters the results, then determines the output:

#"Renamed Columns3" = //your previous step here
fnMatchingList = (MyList) =>
    let
        AllLists = #table(type table [#"ListName"=text, #"ListValues"=list], 
            {{"FoodCompanies",{"Nestlé", "Pepsico", "Unilever"}},
             {"ClothingCompanies", {"Nike", "Ralph Lauren", "Old Navy"}},
             {"TechCompanies",{"Apple", "Samsung", "IBM"}}}),
        FullList = Table.ExpandListColumn(AllLists, "ListValues"),
        Match = Table.AddColumn(FullList, "Match", each List.Contains(MyList,[ListValues])),
        Filtered = Table.SelectRows(Match, each ([Match] = true)),
        Output = if Table.RowCount(Filtered) = 1 then Filtered{0}[ListValues] else 
            if List.Distinct(Filtered[ListName]) = List.Distinct(FullList[ListName]) then "AllCompanies" else 
            Text.Combine(List.Distinct(Filtered[ListName]),", ")
        in
            Output,
#"Added Matching List" = Table.AddColumn(#"Previous Step", "taglist", each if [tag] = null or [tag] = "" then "(Empty Tag)" else fnMatchingList(Text.Split([tag],", ")))

Edit: to aid understanding, here's a standalone query which you can step through, to see what the function is actually doing:

let
    MyList = {"Pepsico", "Nike"},
    AllLists = #table(type table [#"ListName"=text, #"ListValues"=list], 
        {{"FoodCompanies",{"Nestlé", "Pepsico", "Unilever"}},
         {"ClothingCompanies", {"Nike", "Ralph Lauren", "Old Navy"}},
         {"TechCompanies",{"Apple", "Samsung", "IBM"}}}),
    FullList = Table.ExpandListColumn(AllLists, "ListValues"),
    Match = Table.AddColumn(FullList, "Match", each List.Contains(MyList,[ListValues])),
    Filtered = Table.SelectRows(Match, each ([Match] = true)),
    Output = if Table.RowCount(Filtered) = 1 then Filtered{0}[ListValues] else 
        if List.Distinct(Filtered[ListName]) = List.Distinct(FullList[ListName]) then "AllCompanies" else 
        Text.Combine(List.Distinct(Filtered[ListName]),", ")
in
    Output

Upvotes: 1

Related Questions