cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EmPi
Frequent Visitor

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. 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.)

StoreCategory
AmazonFun
AldiFood Store
EdekaFood Store
PocoFurniture
Eurofit24Gym
1 ACCEPTED SOLUTION
ziying35
Impactful Individual
Impactful Individual

@EmPi 

// 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

View solution in original post

10 REPLIES 10
ziying35
Impactful Individual
Impactful Individual

@EmPi 

// 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
EmPi
Frequent Visitor

@ziying35, @Rocco_sprmnt21 , @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

@EmPi

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

Rocco_sprmnt21
Community Champion
Community Champion

define a storeDict:

 

image.png

 

then translate only the words your dict contains:

 

image.png

 

 

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"

 

 

 

Hi @EmPi ,

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.

 

 

@Rocco_sprmnt21 

That's what I thought.

Rocco_sprmnt21
Community Champion
Community Champion

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)

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
Super User
Super User

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.

EmPi
Frequent Visitor

There is no priority.

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors