Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.