cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
myti Regular Visitor
Regular Visitor

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

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

 

Untitled1.jpgTable 1Untitled2.jpgTable 2

3 REPLIES 3
Highlighted
Super User
Super User

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

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




myti Regular Visitor
Regular Visitor

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

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

Super User
Super User

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

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"

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries