3

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!

user3682983
  • 177
  • 2
  • 14

1 Answers1

3

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 &.

Alejandro Lopez-Lago - MSFT
  • 4,014
  • 20
  • 25
  • What if my interaction_id columns has values in cells a1,b1,c1. Is there any way to include all the values in the query ? – user3682983 Sep 01 '15 at 15:16
  • Remove the INTERACTION_ID step and use this for the query: `"select * from ish.ticket where interaction_id in (" & List.Accumulate(List.Skip(#"Changed Type"[INTERACTION_ID], 1), Number.ToText(#"Changed Type"[INTERACTION_ID]{0}), (state, current) => state & ", " & Number.ToText(current)) & ")"` This loops through the column and appends commas after the first element. This will only work if you have values in your column. – Alejandro Lopez-Lago - MSFT Sep 01 '15 at 18:21