Forum Discussion
Extract Text from referenced column
Hi everybody, hope you are doing good. In my Power BI report I want to filter my bank transfers according certain categories, e. g. food, medicine, petrol, gym, ... I got two tables: 1. ...
Anonymous
// Table1 let Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("jc0xDoIwGIbhq3zpqmkicXIr2IBRKBEd1DhU+dGGUEhpJ+PdXTxAD/C87+3DUm17eKft3JGDsZ5sSy2m4KZxJrZhohRXVaEWl1JWpwbyfFS1RMMzLji2kn2XMZXDdoe8TAsskCmOfR7nKLixMz5ZIx8eRZyRLfUajQ9dh1WSxKFaZQrSWGeebx/sax40ud7T/3v/AQ==",BinaryEncoding.Base64),Compression.Deflate))), fx = (str)=> Table2[Category]{List.PositionOf(Table2[Store], str, 0, (x,y)=>Text.Contains(y,x,Comparer.OrdinalIgnoreCase))}?, dic = Table.Buffer(Table2), result = Table.AddColumn(Source, "Category", each fx([Bank transfer intended purpose])) in result // Table2 let Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65WCi7JL0pVslJyzE2sys9T0lFyTixJTc8vqgSKuZXmKdXqIKnJSclEU5Gfn6IAkUZW6JqSmp1IlMqA/OR8dEuL8jJLStFNLC3KT8ssMTJBVexematUGwsA",BinaryEncoding.Base64),Compression.Deflate))) in Source
what if your transactions mention multiple keywords? Like
"pumped petrol/benzin/gas at the Aral station next to Edeka" ?
Is there a priority?
What you usually do is either a fuzzy join between the tables, or a custom function that enumerates table 2 for each row of table 1 and does the comparison.
There is no priority.