Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
// 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
// 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
@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
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
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"
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.
@Anonymous
That's what I thought.
I added two tables.
I would really appreciate if you could give me more hints or a direct solution for this issue.
Greetings
EmPi
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.