How To Display Multiple Values In One Cell For Conditional Column

[p54]

Power BI Community,

I've created a conditional column in Power Query, which assigns a number based on text found in another column. And multiple conditions exist. For example, the word "apple" is assigned 1. The word "tree" is assigned 4. However, only one value shows for each cell. There should be multiple values showing for some cells. As it applies to this example, Apple Tree is found in the referenced column, but only assigning the value of 1 for apple, but not both 1 and 4 for apple tree. How do I show multiple values in one cell, based on the multiple conditions? Thanks.

[p54]

Thanks. I'll try it. I'm just wondering why the Power Query application has not provided a simple IF AND THEN solution that will look for, and display, mulitple values. It seems that Power Query can separate multiple values that have already been displayed through a delimiter function. But not the reverse. Or.....there are 42 words that are assigned 42 unique numbers. Perhaps there is a way to display multiple values with a "range" from 1-42, looking for all numbers in the range? Thanks for the solution. I'll try it.

[m_dekorte]

Hi @p54,

You could give something like this a go. It takes another table as argument with two columns, first the string to look for and second the string to return. I've called this table "lookups" in this example.

let
    lookups = Table.FromColumns(
        {{"apple", "tree"}, {"1", "4"}},
        type table [LookFor = text, Return = text]
    ),
    Source = Table.FromRows( {{"Apple tree"}}, type table [Value = text]),
    AddCustom = Table.AddColumn(Source, "Custom", each 
        Text.Combine( 
            List.ReplaceMatchingItems( Text.Split( [Value], " "), 
                Table.ToRows( lookups ), 
                Comparer.OrdinalIgnoreCase 
            ), ", "
        ), type text
    )
in
    AddCustom

Power Query is case sensitive, this will ignore case and return.

Ps. If this helps solve your query please mark this post as Solution, thanks!

[goldenfisherman]

hi @m_dekorte I am new to the community and first of all I would like to thanks for your solution because I was looking for it for my same question.

However, when I tried to add a new query, it results in the same table as your screenshot, but when I use that same query to the add Custom column to the table where I would like to have the new condition column, the results show in all rows as "table" instead of 1,4. 

Any idea what could be the reason? I assume it could be due to the lookup table as I was wondering if I even need the lookup table since the lookup value is already from a column which I would like to reference to.

[m_dekorte]

Hi @goldenfisherman 

How you've described it, it doesn't sound similar to this case... best to provide an example and describe what you expect as outcome.

I've added comments to the code, explaining what's happening there. BTW when you reference a table, which is just another value in M, it will show as 'table' on each row.

// Source = table to add a new column to
AddCustom = Table.AddColumn(Source, "Custom", each 
    Text.Combine( 
        // Text.Split( [Value], " ") = a list with items to look for
        List.ReplaceMatchingItems( Text.Split( [Value], " "), 
        // Table.ToRows( lookups ) = a list with a nested list for each row
        // each nested list has this format: {lookfor, returnvalue}
            Table.ToRows( lookups ), 
            Comparer.OrdinalIgnoreCase 
        ), ", "
    ), type text
)