Forum Discussion
create alias table
I've tweaked the fnVLookup to just do a text match search and pick the first entry by an order.
(lookup_value as any, table_array as table, col_index_number as number, optional array_order_column as number) as any => let /*Provide optional sort column if user didn't */ sortColNo = if array_order_column = null then 0 else array_order_column - 1 , /*Get name of return column */ Cols = Table.ColumnNames(table_array), ColTable = Table.FromList(Cols, Splitter.SplitByNothing(), null, null, ExtraValues.Error), ColName_match = Record.Field(ColTable{0},"Column1"), ColName_return = Record.Field(ColTable{col_index_number - 1},"Column1"), ColName_Sort = Record.Field(ColTable{sortColNo}, "Column1"), /*Find closest match */ SortData = Table.Sort(table_array,{{ColName_Sort, Order.Ascending}}), RenameLookupCol = Table.RenameColumns(SortData ,{{ColName_match, "Lookup"}}), Matches = Table.SelectRows(RenameLookupCol, each Text.Contains(lookup_value, [Lookup])), Return = if Table.IsEmpty(Matches)=true then "#N/A" else Record.Field(Matches{0}, ColName_return) in Return
Memorable MemberThanks KenPuls
It's an interesting and funny video. I didn't know what it was at the start. He's definately got a career in voice over work.
I'm doing string lookups but that works if the when the keyword is at the start. Now that I look again it's not doing what I thought as I was expecting it anywhere in the string. However all my test strings started with the keywords so everything worked.
I might have another go to either switch to the append method or rework your function to search in the string.
In my example it's reasonable to limit it to the start of the string as it's names of SSIS Packages and most people put the type at the front.
Memorable MemberI've tweaked the fnVLookup to just do a text match search and pick the first entry by an order.
(lookup_value as any, table_array as table, col_index_number as number, optional array_order_column as number) as any =>
let
/*Provide optional sort column if user didn't */
sortColNo =
if array_order_column = null
then 0
else array_order_column - 1 ,
/*Get name of return column */
Cols = Table.ColumnNames(table_array),
ColTable = Table.FromList(Cols, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ColName_match = Record.Field(ColTable{0},"Column1"),
ColName_return = Record.Field(ColTable{col_index_number - 1},"Column1"),
ColName_Sort = Record.Field(ColTable{sortColNo}, "Column1"),
/*Find closest match */
SortData = Table.Sort(table_array,{{ColName_Sort, Order.Ascending}}),
RenameLookupCol = Table.RenameColumns(SortData ,{{ColName_match, "Lookup"}}),
Matches = Table.SelectRows(RenameLookupCol, each Text.Contains(lookup_value, [Lookup])),
Return = if Table.IsEmpty(Matches)=true
then "#N/A"
else Record.Field(Matches{0}, ColName_return)
in Return