Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |