Unable to Combine Data – Query references other queries, so it may not directly access a data source

Hi,

I am attempting to use the result of one Query as a parameter within the where clause of a SQL statement in another query. This works in Power BI Desktop, but I am encountering the following error in the Power BI service when attempting a refresh.

• [Unable to combine data] Section1/Query2/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

   

  In short, what I am attempting to do:

  1. Query1 returns a table of values
  2. A single column within Query1 is converted into a comma separated list of type string as follows: List1 = 'ID1','ID2,'ID3'
  3. Query2 uses this list in the where clause of a SQL statement: SELECT * FROM table1 WHERE ID IN (List1)

      

     Query1: 

     let
     Source = Smartsheet.Tables(),
     #"XXXXXXXXXXXXXXX" = Source{[Key="XXXXXXXXXXXXXXX"]}[Data],
     #"YYYYYYYYYYYYYYYY" = #"XXXXXXXXXXXXXXX"{[Key="YYYYYYYYYYYYYYYY"]}[Data],
     #"ZZZZZZZZZZZZZZZ" = #"YYYYYYYYYYYYYYYY"{[Key="ZZZZZZZZZZZZZZZ"]}[Data]

     in
     #"ZZZZZZZZZZZZZZZ"

      

     Query2: 

     let

         Source  = Query1,

         #"Removed Other Columns" = Table.SelectColumns(Source,{"ID"}),

         #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([ID] <> null)),

         #"Added Custom" = Table.AddColumn(#"Filtered Rows", "left", each "'"),

         #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"left", "ID"}),

         #"Added Custom1" = Table.AddColumn(#"Reordered Columns", "right", each "'"),

         #"Inserted Merged Column" = Table.AddColumn(#"Added Custom1", "ID.1", each Text.Combine({[left], Text.From([ID], "en-US"), [right]}, ""), type text),

         #"Removed Other Columns1" = Table.SelectColumns(#"Inserted Merged Column",{"ID.1"}),

         #"ID 1" = #"Removed Other Columns1"[ID.1],

         #"liststep1" = Lines.ToText(#"ID 1",","),

         #"liststep2" = Text.Start(#"liststep1",Text.Length(#"listep1")-1),

        

         List1 =#"liststep2",

      

         SQL = Sql.Database("DataSource", "db",

         [Query="SELECT  * 

         FROM table1

         WHERE ID IN

         (" & List1 & ")

         "])

     in

         SQL

      

     To overcome the errors in Power BI Desktop, I have set all data privacy settings to none and set Options -> Privacy to "Always ignore Privacy Level Settings". This setup refreshes successfully in desktop. I have also disabled the load of Query1.

      

     I've also tried the strategy outlines here with Value.NativeQuery https://blog.crossjoin.co.uk/2017/06/26/data-privacy-settings-in-power-bipower-query-part-3-the-formula-firewall-error/

     , but I am unable to get this to return any data when the parameter is set to a list with commas i.e. 'ID1','ID2,'ID3'.

      

     Is using the result of one query as a parameter in another supported in Power BI Service? If so, how can I overcome these errors? Thanks so much!