Tags in a table

[Craigc3814]

I have a training table that shows each training class that was delivered. We are adding tags to the data table to let us know what the training consisted of, was it a safety training, a policy training etc. Some trainings will have multiple tags. So it could be a safety training, and a policy training at the same time. How can I organize the tags so that power query will know, that no matter the position of the word safety, it will count the training as a safety training.

An example being maybe training session A was only a safety related training. Maybe training session B was a policy, safety, and operation training. I want to be able to eventually pivot the data to show how many safety trainings we had, how many policy trainings we had, etc. A class should be counted twice if it had more than one tag. The position of the word is throwing me off.

 

[FranzV]

Hi Craig,

I'm sharing with you a Custom Function I have used in similar scenarios to classify products based on their description. You can also find an example of how you can implement the function in a query.

The function has the code shown below. The comments should explain how it works, but feel free to ask in case something is not clear or is confusing. I like my functions clear and flexible, but I am not much of an expert so there might be better ways to achieve these goals.

// fnMultiContains
(
    String as text, // The string to look within.
    WordsToCheck as list, // The words to look for.
    optional CaseSensitive as nullable logical,
    optional Delimiter as nullable text, 
        /* The function will look for complete words separated by a space
        unless otherwise specified. */
    optional DefaultNull as nullable text, 
        // Show this value instead of null for searches with no results.
    optional ReturnTable as nullable logical 
        /* Changes the resulting list into a table with one column for each 
        search term and true or false values for each row. */
) as any =>


let
    caseSensitive = // Defaults to false.
        if CaseSensitive is null then false else CaseSensitive as logical,
    delimiter = // Defaults to space (" ").
        if Delimiter is null then " " else Delimiter as text,
    returnTable = // Defaults to false.
        if ReturnTable is null then false else ReturnTable as logical,
    WordList = // Removes punctuation from the end and splits into a list.
        Text.Split( 
            Text.TrimEnd( String, { ".", ",", "?", "!", ":", ";" } ),
            delimiter 
        ) as list,
    TransformCase = // Lowercases every word for case insensitive comparisons.
        if caseSensitive 
        then WordList 
        else List.Transform( WordList, Text.Lower ) as list,
    CheckWords = // Lowercases every word for case insensitive comparisons.
        if caseSensitive 
        then WordsToCheck 
        else List.Transform( WordsToCheck, Text.Lower ) as list,
    CheckMatches = // Checks each word against the string and returns a {logical} 
        List.Transform(
            CheckWords ,
            each List.Contains( TransformCase , _ )
        ) as list,
    FullList = // Changes true for the search term or false for null.
        List.Transform(
            List.Zip( {CheckMatches, WordsToCheck} ),
            each if _{0} then _{1} else null
        ) as list,
    MatchCount = // Counts the number of terms found in the string.
        List.NonNullCount( FullList ),
    AsList = // If no match was found returns the DefaultNull else the matches.
        if MatchCount = 0 
        then { DefaultNull }
        else List.RemoveNulls( FullList ) as list,
    AsTable = // Uses the search terms as headers and the true/false as values.
        Table.FromRows( { CheckMatches }, WordsToCheck ) as table,
    MultiContains = // Selects the list or the table based on ReturnTable.
        if returnTable then AsTable else AsList
in
    MultiContains

This function can return a list of the matching items in each row that you can then expand to have a single column that specifies the kind of training or a table with one column for each category that contains TRUE if the row contains the word in the header or FALSE if otherwise. This is specified in the 5th argument of the function.

I am not entirely sure, but I believe the List output of the function is what you are looking for, so each training will have one or more rows, depending on the number of categories that training falls into.

You can use the function in its List form using a code similar to:

// TrainingType
let
    DescriptionList = { //Samples for testing.
        "another training", 
        "return policy training", 
        "head safety and discount policy training", 
        "hand safety training", 
        "unknown training", 
        "Safety first, then more safety.", 
        "Use the right policy?" 
    } as list,
    YourTable = 
        Table.FromList( 
            DescriptionList, 
            Splitter.SplitByNothing(), 
            type table [TrainingDescription= text] 
        ) as table,
    AddTrainingType = 
        Table.ExpandListColumn( //Expands the list with a row for each term found.
            Table.AddColumn( //Adds a column to YourTable.
                YourTable, //The name of the table or the previous step.
                "TrainingType", //The new column's name must match 
                each fnMultiContains( //Function with the following arguments:
                    [TrainingDescription], //String: column to check.
                    { "Safety", "Policy" }, //WordsToCheck: you can add any word.
                    null, //CaseSensitive: accepts true or false.
                    null, //Delimiter: uses space if not specified.
                    "Other" //DefaultNull: gives a value when nothing is found.
                ),
                type {text}
            ), 
            "TrainingType" 
        )
in
    AddTrainingType

The comments should guide you through. The query results in a table like this one:

[TABLE="class: head"]
<tbody>[TR]
[TD]TrainingDescription[/TD]
[TD]TrainingType[/TD]
[/TR]
[TR]
[TD]another training[/TD]
[TD]Other[/TD]
[/TR]
[TR]
[TD]return policy training[/TD]
[TD]Policy[/TD]
[/TR]
[TR]
[TD]head safety and discount policy training[/TD]
[TD]Safety[/TD]
[/TR]
[TR]
[TD]head safety and discount policy training[/TD]
[TD]Policy[/TD]
[/TR]
[TR]
[TD]hand safety training[/TD]
[TD]Safety[/TD]
[/TR]
[TR]
[TD]unknown training[/TD]
[TD]Other[/TD]
[/TR]
[TR]
[TD]Safety first, then more safety.[/TD]
[TD]Safety[/TD]
[/TR]
[TR]
[TD]Use the right policy?[/TD]
[TD]Policy[/TD]
[/TR]
</tbody>[/TABLE]


I hope it helps.