Power Query manual M code modification - help needed

[Matt Allington]

Hi

I am trying to write a recursive query that will search for strings (column F) in a single column (column C). If the string is found, then a new column should create the category (Column E).

I have produced a sample workbook and written the starting code. Currently I have a single line of code that searches for "Apples" and adds the word "Fruit" into a new column. Now I am thinking that I should be able to manually alter the query I have using the "_" operator and pass a list of search terms (Column F) to the existing single step and have it return a table of all the matching values (sourced from column E) into a single column. I think this should be possible (Marcel, Imke)? however it is beyond my current understanding.

I would appreciate any help, even if you can tell me it can't be done.

Matt

Sample http://xbi.com.au/files/forum_question.xlsx

 

[citizenbh]

It might help:

https://www.mrexcel.com/forum/power-bi/993320-tags-table.html

 

[Comfy]

Hi Matt,

Is this any help?

Created a new Query called "Output"

let
    Source = Subst_table,
    #"Added Custom1" = Table.AddColumn(Source, "AllContents", each Data),
    #"Expanded AllContents" = Table.ExpandTableColumn(#"Added Custom1", "AllContents", {"Contents"}, {"Contents"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded AllContents", each Text.Contains([Contents], [Search])),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Search"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Category", type text}, {"Contents", type text}})
in
    #"Changed Type"

At the moment this will return multiple rows for multiple matches, would concatenation be more desirable?

Excel 2010
	B	C
2	ID	Contents
3	1	Apples, Apples anyone?
4	2	Oranges make good juice
5	3	Big Steaks are Juicy
6	4	I like Sausages
7	5	Bananas and Sausages
8	6	apples for sale
9
Sheet1

Excel 2010
	F	G
1	Category	Contents
2	Fruit	Apples, Apples anyone?
3	Fruit	apples for sale
4	Fruit	Oranges make good juice
5	Meat	Big Steaks are Juicy
6	Meat	I like Sausages
7	Meat	Bananas and Sausages
8	Fruit	Bananas and Sausages
9
Sheet3

 

[Comfy]

Too slow to edit.

Updated to concatenate multiple results

let
    Source = Subst_table,
    #"Added Custom1" = Table.AddColumn(Source, "AllContents", each Data),
    #"Expanded AllContents" = Table.ExpandTableColumn(#"Added Custom1", "AllContents", {"Contents"}, {"Contents"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded AllContents", each Text.Contains([Contents], [Search])),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Search"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Contents"}, {{"Grouped", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows" , "Result", each Text.TrimEnd(List.Accumulate([Grouped][Category], "", (state, current) => state & current & ", "), {"," , " "})),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Grouped"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"Contents", type text}, {"Result", type text}})
in
    #"Changed Type"

Excel 2010
	F	G
1	Contents	Result
2	Apples, Apples anyone?	Fruit
3	apples for sale	Fruit
4	Oranges make good juice	Fruit
5	Big Steaks are Juicy	Meat
6	I like Sausages	Meat
7	Bananas and Sausages	Meat, Fruit
8
Sheet3

 

[citizenbh]

Ok! This is my solution
Code for FirstAttempt:

let
    Source = Data,
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Subst_table ),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Category", "Search"}, {"Category", "Search"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Custom", "Custom", each if Text.Contains([Contents], [Search]) then "1" else "0" ),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = "1")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Search"})
in
    #"Removed Columns"

 

[Matt Allington]

Thanks to you both citizenbh and Comfy. I hadn't thought about the approach you both have suggested but I do like it. This will solve my issue and I will use it. I would still be interested in knowing if a solution using my idea is possible if anyone else would like to contribute.