Extract Text from referenced column

[Anonymous]

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. Table) Text with the intended purpose of my bank transfers (text column)

2. Table) Mapping of stores to categories

E. g.  ARAL --> petrol station; EDEKA --> food store; ASIA Sushi --> Restaurant 

The problem is that the intended purpose of 1. Table) has more text included than the listed stores. So in the first step I need a column that supports me extract the corresponding store name from the intended purpose. With this support column I get look up the corresponding category in the second table.

In the past I did this according the following video: https://www.youtube.com/watch?v=JkyR6s0aBok&t=246s

Is it possible to generate such a column in Power Query directly? Or do you have any other pragmatic solution?

Greetings

EmPi

Table 1.)

Bank transfer intended purpose

AMAZON PAYMENTS EUROPE S.C.A. DE

ALDI GMBH + CO. KG

eurofit24 GmbH

Edeka Stuff 122

POCO Einrichtungsmaerkte GmbH

Table 2.)

Store	Category
Amazon	Fun
Aldi	Food Store
Edeka	Food Store
Poco	Furniture
Eurofit24	Gym

[ziying35 · Accepted solution]

@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

[Anonymous]

@ziying35, @Anonymous , @lbendlin thank you for your answers. I just tried the code but it is not working properly.

I´am very new to the topic so I have some open points. For my current understanding: 

Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65WCi7JL0pVslJyzE2sys9T0lFyTixJTc8vqgSKuZXmKdXqIKnJSclEU5Gfn6IAkUZW6JqSmp1IlMqA/OR8dEuL8jJLStFNLC3KT8ssMTJBVexematUGwsA",BinaryEncoding.Base64),Compression.Deflate)))

This is the code for my data source. In this case from a JSON document. If I am getting my data from an excel file it would look like this: (I´am asking because all my data is from excel)

Source = Excel.Workbook(File.Contents("C:\Users\ABC\Desktop\PowerBI_Projekt\Finanzen\Buchungen\Ordnerpfade.xlsx"), null, true),

I also did some changes at the columns like:

#"Changed Type" = Table.TransformColumnTypes(tab_QuelleSparkasse_Table,{{"Sparkasse", type text}}),

(Table1 = tab_QuelleSparkasse)

As also the categories are from excel the code for Table2 looks like: (Table2 = tab_KatRef)Source = Excel.Workbook(File.Contents("C:\Users\ABS\Desktop\PowerBI_Projekt\Finanzen\Buchungen\Kategorien.xlsx"), null, true),
tab_KatRef_Table = Source{[Item="tab_KatRef",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(tab_KatRef_Table,{{"Kategorie", type text}, {"Namen", type text}})
in
#"Changed Type"

For my understanding I "only" have to add

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

to table 1?

As follows:

let
Source = Excel.Workbook(File.Contents("C:\Users\pme2abt\Desktop\PowerBI_Projekt\Finanzen\Buchungen\Ordnerpfade.xlsx"), null, true),
tab_QuelleSparkasse_Table = Source{[Item="tab_QuelleSparkasse",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(tab_QuelleSparkasse_Table,{{"Sparkasse", type text}}),

fx = (str)=> tab_KatRef[Kategorie]{List.PositionOf(tab_KatRef[Namen], str, 0, (x,y)=>Text.Contains(y,x,Comparer.OrdinalIgnoreCase))}?,
dic = Table.Buffer(tab_KatRef),
result = Table.AddColumn(Source, "Kategorie", each fx([Bank transfer intended purpose]))

in

 result

[Bohumil_Uhrin]

@Anonymous, 

Source in result row replace with #"Changed Type"

tab_KatRef in fx row replace with dic - this change is not necessary, but it will speed up the query (otherwise the buffer is not used)

So your code should look like this for Table1, changes are bolded:

let
Source = Excel.Workbook(File.Contents("C:\Users\pme2abt\Desktop\PowerBI_Projekt\Finanzen\Buchungen\Ordnerpfade.xlsx"), null, true),
tab_QuelleSparkasse_Table = Source{[Item="tab_QuelleSparkasse",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(tab_QuelleSparkasse_Table,{{"Sparkasse", type text}}),

fx = (str)=> dic[Kategorie]{List.PositionOf(dic[Namen], str, 0, (x,y)=>Text.Contains(y,x,Comparer.OrdinalIgnoreCase))}?,
dic = Table.Buffer(tab_KatRef),
result = Table.AddColumn(#"Changed Type", "Kategorie", each fx([Bank transfer intended purpose]))

in

 result

[Anonymous]

define a storeDict:

then translate only the words your dict contains:

the code for dict:

let
    dict = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsxNrMrPU9JRcivNU4rVAQrkpGSCuPn5KQrBJflFqWBR15TU7ERM4YD85Hyw3qK8zJJSmNrSovy0zBIjE6CMe2WuUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Store = _t, Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(dict,{{"Store", type text}, {"Category", type text}})
in
 Record.FromList(dict[Category],List.Transform(dict[Store],Text.Lower))

the code to translate selected words:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcvRCsIgAEbhV/nZbSEkvYA5cVFOybqosYuttGRsgen7F9LtxzldVzHFbrqFYVcl2rOFuJy0EbCEE0ZQi6pf/6JjvYdUuwYrcE1wkIVdjm8fEt1CzmNTSDzcNMCm7D02lBYzmmuIsMRwf6W8PD/z4OKU3P/qvw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Bank transfer intended purpose" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Bank transfer intended purpose", type text}}),
Table.AddColumn(#"Changed Type", "Category", each Text.Combine(List.Transform(Text.SplitAny([#"Bank transfer intended purpose"], Text.Combine(List.Difference({" ".."@"},{"0".."9"}))), each Record.FieldOrDefault(storeDict, Text.Lower(_))),";"))
in
    #"Added Custom"

[Anonymous]

Hi @Anonymous ,

you make a request for assistance, you get several answers and AFTER MORE THAN A MONTH, you write:
"I just tried the code but it is not working properly."
Aside from the fact that it is not clear what is not working, I believe that none of those who answered you remember anything about the subject.
I'm sorry but I no longer want to reread the posts from a month ago.

[ziying35]

@Anonymous 

That's what I thought.

[Anonymous]

although no answer has been promoted to solution by @asas27 ,

here

you may find some helpful hints on how to set the solution.
If you want a "complete" solution, you should post some example table with the expected result.
All in a form that can be copied easily (no images)

[Anonymous]

I added two tables.

I would really appreciate if you could give me more hints or a direct solution for this issue.

Greetings
EmPi

[lbendlin]

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.

[Anonymous]

There is no priority.