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 categoriesE. 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=246sIs it possible to generate such a column in Power Query directly? Or do you have any other pragmatic solution? GreetingsEmPi Table 1.)Bank transfer intended purposeAMAZON PAYMENTS EUROPE S.C.A. DEALDI GMBH + CO. KGeurofit24 GmbHEdeka Stuff 122POCO Einrichtungsmaerkte GmbH Table 2.)StoreCategoryAmazonFunAldiFood StoreEdekaFood StorePocoFurnitureEurofit24Gym

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

Extract Text from referenced column | Microsoft Fabric Community

10 Replies

Anonymous

Not applicable

5 years ago

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
Not applicable
5 years ago

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
  Impactful Individual
  5 years ago
  Anonymous
  That's what I thought.

ziying35
Impactful Individual
5 years ago
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
  Not applicable
  5 years ago
  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
    Helper II
    5 years ago
    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
lbendlin
Super User
6 years ago
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
  Not applicable
  5 years ago
  There is no priority.
Anonymous
Not applicable
6 years ago
although no answer has been promoted to solution by Anonymous ,
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
  Not applicable
  5 years ago
  I added two tables.
  
  I would really appreciate if you could give me more hints or a direct solution for this issue.
  
  Greetings
  EmPi

Forum Discussion

Extract Text from referenced column

10 Replies

Related content

Extracting complicated text from a column

Extract Data from sharepoint

Extract 2 digits from a column

Extract Cetrain Characters from Text Column

Data Extraction from Oracle

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