Custom Column to Return Value Comparing List with Corresponding List

[Anonymous]

Hello everyone!

Good morning! I'm trying to add a custom column [Customer Column_Output] to check through a list of text ([Commodity Description_List]) with a corresponding list (#"Type"[Description]) and return the value that is part of the #"Type"[Type]

Type

Description	Type
40gp	40ft
53hq	53ft

Main Table

Commodity Description_List	Custom Column_Output
{40gp abc, def, ghi}	{40ft, null, null, null}
{a 53hq, 40gp dh, abc}	{53ft, 40ft, null, null}

I definitely will appreciate any help and thank you for your time!

[Vijay_A_Verma]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    List1 = {"40gp abc", "def", "ghi"},
    List2 = {"a 53hq", "40gp dh", "abc"},
    Source = Table.FromRecords({
    [Commodity Description_List= List1],
    [Commodity Description_List= List2]
}),
    #"Added Custom" = Table.AddColumn(Source, "Custom", (m)=> List.Generate(()=>[x=m[Commodity Description_List]{0},i=0], each [i]<List.Count(m[Commodity Description_List]), each [i=[i]+1, x=m[Commodity Description_List]{i}], each try Type[Type]{List.PositionOf(List.Transform(Type[Description],(r)=>Text.Contains([x],r)),true)} otherwise null))
in
    #"Added Custom"

[Anonymous]

Hi Vijay, thank you for that quick response. I statred a new query and pasted in the code and the error message was;

Expression.Error: The name 'Type' wasn't recognized. Make sure it's spelled correctly.

[Vijay_A_Verma]

Test code for Type table (Hence, your query should be named Type)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjFIL1DSAVJpJUqxOtFKpsYZhUC+qTGIHwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Description = _t, Type = _t])
in
    Source

[Anonymous]

Hi Vijay! I found out the issue and it was because I did not have the table "Type"[Description] and "Type"[Type]. Now I do not have any error message but the custom column created now shows Function with each row showing "function (m as any) as any

I have included a screenshot for easy reference and thank you very much for your help again!

 

[Vijay_A_Verma]

Please post your complete query.

[Anonymous]

Hi Vijay, here you go

let
List1 = {"40gp abc", "def", "ghi"},
List2 = {"a 53hq", "40gp dh", "abc"},
Source = Table.FromRecords({
[Commodity Description_List= List1],
[Commodity Description_List= List2]
}),

#"Added Custom" = Table.AddColumn(Source, "Type", each (m)=> List.Generate(()=>[x=m[Commodity Description_List]{0},i=0], each [i]<List.Count(m[Commodity Description_List]), each [i=[i]+1, x=m[Commodity Description_List]{i}], each try #"Type"[Type]{List.PositionOf(List.Transform(#"Type"[Description],(r)=>Text.Contains([x],r)),true)} otherwise null))
in
#"Added Custom"