Search code examples
expressionpowerquerynativequery

Power Query -- list value in native query

im having a problem using the "in" operator in a native query, where the in argument is a list retrieved by drilling a excel column table:

let
   var = Number.ToText(fnFuncao("externalcode")),
   Coluna= Excel.CurrentWorkbook(){[Name="Pendente"]}[Content],
   #"Changed Type" = Table.TransformColumnTypes(Coluna,{{"PARAMETER", Int64.Type}, {"INTERACTION_ID", Int64.Type}, {"INTERACTION_ITEM", Int64.Type}}),
   INTERACTION_ID = #"Changed Type"[INTERACTION_ID][0],
   Source = Oracle.Database("somp", [Query="select * from ish.ticket where    interaction_id in (" & INTERACTION_ID & ") "])
in
    Source

Error:
    Expression.Error: We cannot apply operator & to types Text and List.
    Details:
    Operator=&
    Left=select * from ish.ticket where interaction_id in (
    Right=List

Any way to work around this ? Thanks!

Solution

  • I don't know why INTERACTION_ID is treated like a list. These two changes should fix your issue:

    1. Change INTERACTION_ID to #"Changed Type"[INTERACTION_ID]{0},
    2. Set Query to "select * from ish.ticket where interaction_id in (" & Number.ToText(INTERACTION_ID) & ")"

    The first change uses the list access operator {} instead of the record access operator []. The second change converts INTERACTION_ID to a Text value so that it can be added to other Text values with &.