Forum Discussion

SDHaas's avatar
SDHaas
Frequent Visitor
4 years ago
Solved

Return the first matching value from range using List

Hello.   I have two tables ('Assignment', 'Type'). I'm trying to search a range in the first table and return the first match from the second table. I'm currently using "List.ContainsAny" and retur...
Need Help
  • Jakinta's avatar
    Jakinta
    4 years ago

    You can add this step in Assignment query.

     

    = Table.AddColumn( PreviousStepName, "TYPE", each let x=List.Skip(Record.ToList(_),2), y=Record.ToList(_){1}, z=Table.Column(Type, y) in try List.RemoveNulls (List.Intersect( { x,z} )){0} otherwise "")

     

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

Try adding a custom column like this:

List.First(
    List.Intersect(
        {
            Table.Column(Type, [GEO]),
            Record.FieldValues(_)
        }
    )
)

 

Here's a full sample query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfILAxKOTkAiPBDECgYSvr5Awj9UKVYnWskIyPRxhPB1lMJAqoMcYarBKozhpviDhHSUnIMgNFjWBMgIdoMI+EAVQNTExgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, GEO = _t, T1 = _t, T2 = _t, T3 = _t, T4 = _t, T5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"GEO", type text}, {"T1", type text}, {"T2", type text}, {"T3", type text}, {"T4", type text}, {"T5", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "TYPE", each List.First(List.Intersect({Table.Column(Type, [GEO]), Record.FieldValues(_)})), type text)
in
    #"Added Custom"