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 everyone this is my first post and probably not the last one,
I have been struggling to find a solution to my problem.
I hope you can help me with this simple problem.
I have a table with Keywords, Categories and Subcategories like below:
Keywords | Category | Subcategory |
Carrot | Vegetable | Low |
Apple | Fruit | High |
Beef | Meat | Medium |
Then I have a table with all the data like below:
Order number | Ingredients |
5156 | Apples, Carrot |
5158 | Beef, Pasta, Flour |
I would like to search for the keywords on the column "Ingredients" (2nd table), if found look at the 1st table and return either the Category or Subcategory.
Now I have done some research on my own, I'm not asking you to do all the work.
I found 2 solutions but they are only part of the solution, here are the links below:
1st link is about a search function that will search for the keyword but return them if found:
https://www.myonlinetraininghub.com/create-a-list-of-matching-words-when-searching-text-in-power-que...
2nd link is about a vlookup like function, but I'm unable to use it in this situation:
NOTE: For the 1st link I used the "List Found Substrings - Case Insensitive" solution
If possible could you explain to me what you have done or how it works, I don't have much experience in coding, in fact, I want to learn more and this is the perfect opportunity to learn more.
Sincerely,
Pierre Reynaud
Solved! Go to Solution.
Hello @Anonymous
the code works probably fine - we saw this in my first solution. M
aybe there is something wrong with your masterdata. Possible error could be what your columname of your keywords table is not the same as in my code... maybe with some spaces in it at the end or beginning. The same could be for your keywords like " Paypal Payment ".
So without seeing that is impossible to help you anymore - sorry.
What you can do on your side is to try this code (i canceled the error handling) and check out the error it gives you.
let
Helper =
let
Source = Excel.CurrentWorkbook(){[Name="Helper"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Word", type text}, {"Category", type text}, {"Subcategory", type text}})
in
#"Changed Type",
Data =
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Source.Name", type text},{"Date", type text},{"Type", type text},{"Details", type text}, {"Amount Out", type text},{"Amount In", type text},{"Balance", type text},{"Class", type text}})
in
#"Changed Type",
AddCategory = Table.AddColumn(Data, "Categories", each let
GetListOfDetails = List.Transform(Text.Split([Details], ","), each Text.Trim(_)),
GetCategory = List.Transform(GetListOfDetails, (item)=> Table.SelectRows(Helper , each Text.Upper([Word]) = Text.Upper(item))[Category]{0}),
CombineCategories = Text.Combine (GetCategory, ", ")
in
CombineCategories)
in
AddCategory
I hope you are able to solve it with this
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hello @Anonymous
you can add a new column and use this formula. "Keywords" has to be step or the query where you stored the keywords. If your tables are big, then we might enhance a little bit to get a better performance
let
GetListOfIngredients = List.Transform(Text.Split([Ingredients], ","), each Text.Trim(_)),
GetCategory = List.Transform(GetListOfIngredients, (item)=> try Table.SelectRows(KeyWords, each [Keywords] = item)[Category]{0} otherwise "/"),
CombineCategories = Text.Combine (GetCategory, ", ")
in
CombineCategories
Here the complete example (including both of your tables)... but be aware Apples is not Apple
let
KeyWords =
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sKsovUdJRCktNTy1JTMpJBbJ98suVYnWilRwLCsB8t6LSTJAaj8z0DLCEU2pqGpDvm5pYAqZSMktzlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Keywords = _t, Category = _t, Subcategory = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Keywords", type text}, {"Category", type text}, {"Subcategory", type text}})
in
#"Changed Type",
Orders =
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjU0NVPSUXIsKMhJLdZRcE4sKsovUYrVActYAGWcUlPTdBQCEotLEnUU3HLyS4uUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order number" = _t, Ingredients = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Order number", Int64.Type}, {"Ingredients", type text}})
in
#"Changed Type",
AddCategory = Table.AddColumn(Orders, "Categories", each let
GetListOfIngredients = List.Transform(Text.Split([Ingredients], ","), each Text.Trim(_)),
GetCategory = List.Transform(GetListOfIngredients, (item)=> try Table.SelectRows(KeyWords, each [Keywords] = item)[Category]{0} otherwise "/"),
CombineCategories = Text.Combine (GetCategory, ", ")
in
CombineCategories)
in
AddCategory
Outcome
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
@Jimmy801
Thanks for your reply, I have some difficulties using your solution.
The table above was just an example, the table that I'm working on has multiple columns and it won't recognise the column with the data, here is the code after I tried modifying it to my data:
let
Helper =
let
Source = Excel.CurrentWorkbook(){[Name="Helper"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Word", type text}, {"Category", type text}, {"Subcategory", type text}})
in
#"Changed Type",
Data =
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Source.Name", type text},{"Date", type text},{"Type", type text},{"Details", type text}, {"Amount Out", type text},{"Amount In", type text},{"Balance", type text},{"Class", type text}})
in
#"Changed Type",
AddCategory = Table.AddColumn(Details, "Categories", each let
GetListOfDetails = List.Transform(Text.Split([Details], ","), each Text.Trim(_)),
GetCategory = List.Transform(GetListOfDetails, (item)=> try Table.SelectRows(Word, each [Word] = item)[Category]{0} otherwise "/"),
CombineCategories = Text.Combine (GetCategory, ", ")
in
CombineCategories)
in
AddCategory
Also here are the original data column headers:
(tried multiple times to post the table but something is wrong with the HTML)
Source.Name-Date-Type-Details-Amount Out-Amount In-Balance-Class-Category-Subcategory
Hello @Anonymous
as I don't know your real data, I had to simulate it, using your dummy data. For sure you have to connect to your real data, meaning integrate my code somehow. If you have other column names, you have to pay attention to that to. What I can see in your code the variable "Word" is not existing. Then at least you should have another query named word, that has the column you are referencing (Column "word" and "Category" is needed)
But as long as I don't see your exact queries, I cannot help you
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hi Jimmy, (sorry difficulties posting on this forum, had post a blank reply then edit it, take a screenshot of my reply and post as an image)
Hello @Anonymous
when you are using this code, what error gives to you?
let
Helper =
let
Source = Excel.CurrentWorkbook(){[Name="Helper"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Word", type text}, {"Category", type text}, {"Subcategory", type text}})
in
#"Changed Type",
Data =
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Source.Name", type text},{"Date", type text},{"Type", type text},{"Details", type text}, {"Amount Out", type text},{"Amount In", type text},{"Balance", type text},{"Class", type text}})
in
#"Changed Type",
AddCategory = Table.AddColumn(Data, "Categories", each let
GetListOfDetails = List.Transform(Text.Split([Details], ","), each Text.Trim(_)),
GetCategory = List.Transform(GetListOfDetails, (item)=> try Table.SelectRows(Helper , each [Word] = item)[Category]{0} otherwise "/"),
CombineCategories = Text.Combine (GetCategory, ", ")
in
CombineCategories)
in
AddCategory
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hi @Jimmy801,
No error but the new "Categories" column has nothing inside, also I would require a "Subcategory" column too.
Here is a screenshot:
The column is only showing: "/"
Hello @Anonymous
this can have multiple reasons. To undestand that i need your keywords-table too. Be aware that power query is case sensitive. In your case the value "PAYPAL PAYMENT" has to be written exactly in your helper-table in the column "Word"
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hello @Anonymous
okay, then this is the reason. But you should always post your original data, because in your scenario there this request was not needed.
I tried to adapt your code. Hopefully it works, because I cannot test it
let
Helper =
let
Source = Excel.CurrentWorkbook(){[Name="Helper"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Word", type text}, {"Category", type text}, {"Subcategory", type text}})
in
#"Changed Type",
Data =
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Source.Name", type text},{"Date", type text},{"Type", type text},{"Details", type text}, {"Amount Out", type text},{"Amount In", type text},{"Balance", type text},{"Class", type text}})
in
#"Changed Type",
AddCategory = Table.AddColumn(Data, "Categories", each let
GetListOfDetails = List.Transform(Text.Split([Details], ","), each Text.Trim(_)),
GetCategory = List.Transform(GetListOfDetails, (item)=> try Table.SelectRows(Helper , each Text.Upper([Word]) = Text.Upper(item))[Category]{0} otherwise "/"),
CombineCategories = Text.Combine (GetCategory, ", ")
in
CombineCategories)
in
AddCategory
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hi @Jimmy801,
Sorry but I can't really post this information to the public.
I can share it with you in private, if that's okay with you?
The solutio above doesn't seem to work, smae result as before 😞
Hello @Anonymous
the code works probably fine - we saw this in my first solution. M
aybe there is something wrong with your masterdata. Possible error could be what your columname of your keywords table is not the same as in my code... maybe with some spaces in it at the end or beginning. The same could be for your keywords like " Paypal Payment ".
So without seeing that is impossible to help you anymore - sorry.
What you can do on your side is to try this code (i canceled the error handling) and check out the error it gives you.
let
Helper =
let
Source = Excel.CurrentWorkbook(){[Name="Helper"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Word", type text}, {"Category", type text}, {"Subcategory", type text}})
in
#"Changed Type",
Data =
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Source.Name", type text},{"Date", type text},{"Type", type text},{"Details", type text}, {"Amount Out", type text},{"Amount In", type text},{"Balance", type text},{"Class", type text}})
in
#"Changed Type",
AddCategory = Table.AddColumn(Data, "Categories", each let
GetListOfDetails = List.Transform(Text.Split([Details], ","), each Text.Trim(_)),
GetCategory = List.Transform(GetListOfDetails, (item)=> Table.SelectRows(Helper , each Text.Upper([Word]) = Text.Upper(item))[Category]{0}),
CombineCategories = Text.Combine (GetCategory, ", ")
in
CombineCategories)
in
AddCategory
I hope you are able to solve it with this
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hi @Anonymous
There are different ways. I want to understand below questions, so the approach will be different:
the [Ingredients] always have the same delimiter: ","?
the [Subcategory] is based on [Category], like always Fruit-High, Meat-Medium?
Hi @Vera_33 ,
Thank you for replying 🙂
Actually, I'm using the ingredient as an example because the data is sensitive, the "Ingredients" in this case would the details of the bank transactions like "Amazon ..."
The subcategory is not based on the category, so it could be Fruit-Medium or Fruit-Extra, it doesn't follow a rule.
I hope this helps 🙂
I'm hoping this solution can be applied to future projects instead of creating a whole new code for this type of problem.
Hi @Anonymous
Then Ingredient might have multiple values in the lookup table [Keywords], how to determin which Category to put?
Hi @Anonymous
It is another way to lookup values, the Fuzzy Match you can add Threshold, the LookupTable I put here is just for your conveinece, you can delete it and refer to your actual look up table.
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45WMjU0NVPSUXIsKMhJLdZRcE4sKsovUYrVActYAGWcUlPTdBQCEotLEnUU3HLyS4uUYmMB",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [#"Order number" = _t, Ingredients = _t]
),
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Order number", Int64.Type}, {"Ingredients", type text}}
),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
LookupTable =
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45Wck4sKsovUdJRCktNTy1JTMpJBbJ98suVYnWilRwLCsB8t6LSTJAaj8z0DLCEU2pqGpDvm5pYAqZSMktzlWJjAQ==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Keywords = _t, Category = _t, Subcategory = _t]
),
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Keywords", type text}, {"Category", type text}, {"Subcategory", type text}}
)
in
#"Changed Type",
#"Added Custom" = Table.AddColumn(
#"Added Index",
"Custom",
each Table.TransformColumnTypes(
Table.FromList(Text.Split([Ingredients], ",")),
{{"Column1", type text}}
)
),
#"Added Custom1" = Table.AddColumn(
#"Added Custom",
"new",
each [
a = Table.FuzzyNestedJoin(
#"Added Custom"[Custom]{[Index]},
{"Column1"},
LookupTable,
{"Keywords"},
"LookupTable",
JoinKind.LeftOuter,
[IgnoreCase = true, IgnoreSpace = true]
),
b = Table.ExpandTableColumn(
a,
"LookupTable",
{"Category", "Subcategory"},
{"Category", "Subcategory"}
),
c = Table.SelectColumns(b, {"Category", "Subcategory"})
][c]
),
#"Expanded new" = Table.ExpandTableColumn(
#"Added Custom1",
"new",
{"Category", "Subcategory"},
{"Category", "Subcategory"}
),
#"Removed Columns" = Table.RemoveColumns(#"Expanded new", {"Index", "Custom"}),
#"Grouped Rows" = Table.Group(
#"Removed Columns",
{"Order number", "Ingredients"},
{
{"Category", each Text.Combine(_[Category], ", ")},
{"Subcategory", each Text.Combine(_[Subcategory], ",")}
}
)
in
#"Grouped Rows"
Hi @Vera_33 ,
Thank you, I know it might be too much to ask but I'm not very experienced in Power Query, would it be possible for you to map it to the tables that I provided to Jimmy below?
Thanks 🙂
Hi @Anonymous
I actually don't quite understand...I saw your mapping table, but what is your actual data looks like?
If you paste the code to the Advanced Editor
You can click the steps on the right to see what is happening, this one is to split your sample fake data
This is where I said Fuzzy match, so you can specify Threshold and select case insensitive while look up values in your other table
If you can share a sample file, I can work on it
@Vera_33
Sure 🙂 I will give you a piece of my data, I randomise the values and dates.
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.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |