Hi all,
Thank you for an interesting topic to think on :). It gave me an opportunity to test writing a custom function that takes other functions as arguments, it was quite fun :).
This is my version of the ContansEx. It takes three parameters:
1. Required. List of arguments. This can be either:
a. {record, name_of_column_name_to_search_in, {list_of_column_name(s)_to_search_for}}
b. {text_to_search_in, {list_of_text(s)_to_search_for}}
the last parameter in both cases can be a list with only one paramter.
2. Optiona. Comparer function. Takes two text parameters: text_to_search_in and text_to_search_for evaluated dynamically based on the content of #1. Defaults to (x,y)=>Text.Contains(x,y), but can be overloaded with (e.g.) a reversed version (x,y)=>not Text.Contains(x,y) or something even more fancy.
3. Optional. Output fuction. Takes two parametes, which depends on type of the parameters passed in #1:
a. in case of record: record (pased in #1) and current value of the column_name_to_search_for (as text!)
b. in case of list: text_to_search_in and a current value of text_to_search_for.
Output, in any case, a list of values defined by the output function or containing an error message.
The is some error checking/handling in the function, but this can be improved.
This is the function itself (it looks quite lengthy, but you do not have to modify it, just copy and paste):
(parameters as list, optional fComparer as function, optional fOutput as function)=>
let
mComparer = if fComparer = null then (x, y)=> Text.Contains(x, y) else fComparer,
// Assuming following logic:
// 3 parameters used when search done in a record:
// 1st parameter - the record
// 2nd parameter - name of the field to search in
// 3rd parameter - name of the fields containing text to search for
// 2 parameters used when searching in a text from list of values:
// 1st parameter - text to search in
// 2nd parameter - list of texts to search for
InputType = if List.Count(parameters) = 2 then "list" else
if List.Count(parameters) = 3 then "record" else "unknown",
mOutput = if fOutput = null
then
if InputType = "list" then (l as list, i as number)=> l{i}
else if InputType = "record" then (r, fname)=> Record.Field(r, fname)
else (x, y)=> "input parameters do not match any of funciton templates"
else fOutput,
forList =
let
SearchIn = parameters{0},
SearchFor = parameters{1},
Processing = List.Accumulate(SearchFor, {}, (a, n)=> a & {if mComparer(SearchIn, n) then mOutput(SearchFor, List.PositionOf(SearchFor, n)) else null}),
Output = if Value.Type(SearchIn) <> type text then {"SearchIn parameters should be text"} else
if Value.Type(SearchFor) <> type list then {"SearchFor parameter should be list"}
else Processing
in Output,
forRecord =
let
mRecord = parameters{0},
SearchIn = parameters{1},
SearchFor = parameters{2},
Processing = List.Accumulate(SearchFor, {}, (a, n)=> a & {
if mComparer(Record.Field(mRecord, SearchIn), Record.Field(mRecord, n))
then mOutput(mRecord, n) else null}),
Output = if Value.Type(SearchIn) <> type text then {"SearchIn parameters should be text"}
else if Value.Type(SearchFor) <> type list then {"SearchFor parameter should be list"}
//else if Value.Type(mRecord) <> Record.Type then {Value.Type(mRecord)}
else Processing
in Output,
Output = if InputType = "list" then forList
else if InputType = "record" then forRecord
else {"incorrect number of members in the list/first parameter"}
in
Output
This is how this can be called (please note that the last two parameters can be omitted, I use it below only to demonstrate how the default behaviour can be overloaded):
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKTlHSUUoE4iQgBrFTgThNKVYnWqmwPLWopBLILQTiciAuBeIiMD82FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
ListUsingReversedSearch = Table.AddColumn(#"Changed Type", "Custom", each ContansEx({[Column1], {[Column2], [Column3],[Column4],[Column5], [Column6]}}, (a,b)=> not Text.Contains(a,b), (a, b)=> b )),
RecordWithOverloadedOutput = Table.AddColumn(#"Changed Type", "Custom", each ContansEx({_, "Column1", {"Column2", "Column3", "Column4", "Column5", "Column6"}}, null, (a, b)=> {b, List.PositionOf(Record.FieldNames(a),b) } ))
in
RecordWithOverloadedOutput
Kind regards,
JB
