A calculated column to shows if a column contains one of many things in other column

[myti]

Dear Friends,

I have two diffrent data tables as below,I would to check if each row of   column "pagePath" in table 1 contain a string value from column "category" in table 2.If  the result is true, then the  new calculated column will be equal the exact value in table 2.For example  if  pagepath="/clothing/shirts" and category="/clothing/" means the condition is true. the calculted column will be "/clothing/".

Thank you-Myti

Table 1Table 2

[ImkeF]

Hi Myti,

in the example you've provided there would actually be multiple matches in the lookup-table 2:

category = "/clothing/" as well as "/clothing/shirts"

So just to reassure your requirement: What you want is the first match, i.e. the match on the highest level here?

In that case I would start transforming the lookup-table 2 and remove all entries on lower levels that have "parent"-levels, as those would always have priority.

Please confirm if this is the way to go, thank you.

[myti]

Hello @ImkeF,

Thank you very much for your reply.

I think, you are right,please consider the parents only. Later I will think how I can address their second level information.

Thank you,

Regards

Mehdi

[ImkeF]

Hi Mehdi,

hope you can make sense of this code, if not, please come back:

let
    table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs7JL8nIzEvXL87ILCopVorVQRIrKcovLU4tKgZK5heV4JDLyc9LB0sVl2fmlqcmFunD1KCKgs2AWABkpiKx9JPy80EysQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Pagepath = _t]),
    table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs7JL8nIzEtXitVBcPSLMzKLSorBYsXlmbnlqYlFKByggnywglgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Category = _t]),
    table2Transformed = Table.SelectRows(table2, each not Text.Contains([Category], "/")),
    CreateListOfPathElements = Table.AddColumn(table1, "elements", each Text.Split([Pagepath], "/")),
    #"Expanded elements" = Table.ExpandListColumn(CreateListOfPathElements, "elements"),
    MergeWithLookupTable2 = Table.NestedJoin(#"Expanded elements",{"elements"},table2Transformed,{"Category"},"NewColumn",JoinKind.LeftOuter),
    ExpandMatches = Table.ExpandTableColumn(MergeWithLookupTable2, "NewColumn", {"Category"}, {"Category"}),
    #"Removed Duplicates" = Table.Distinct(ExpandMatches, {"Pagepath", "elements"})
in
    #"Removed Duplicates"