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

[jbc22]

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

     , 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!

[Anonymous]

@jbc22 

I was also trying to resolve a similar problem. Followed the steps in this blogpost regarding adjusting privacy settings and it helped.

https://www.meanttobi.com/power-bi-refresh-error-unable-to-combine-data-workaround/ 

[v-kelly-msft]

Hi @jbc22 ，

Put what's in advanced editor about query 1 in below M query I marked with underline:[ Better not directly use query 1 instead of the actual M query,sometimes it will occur problems.]

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

Remember to add a comma  after query 1.

You can turn to the thread below :

https://community.powerbi.com/t5/Desktop/Formula-Firewall-Query-references-other-queries-so-it-may-n...

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[jbc22]

@v-kelly-msft

Thanks for the reply. There is a comma in Query2 I just stripped it out when anonymizing the data. Updated the original post to reflect that as well as Query1 which is below. The link you provided is actually what I followed to overcome the errors in the desktop, but they are persisting in the Service on scheduled refresh. 

Query1

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

in
#"ZZZZZZZZZZZZZZZ"

[v-kelly-msft · Accepted solution]

Hi @jbc22 ，
 
Sorry,I didnt make myself clear in the last reply, modify your Query 2 as below:

let
    Source  = Smartsheet.Tables(),
    Smartsheet.Tables(),
    #"XXXXXXXXXXXXXXX" = Source{[Key="XXXXXXXXXXXXXXX"]}[Data],
    #"YYYYYYYYYYYYYYYY" = #"XXXXXXXXXXXXXXX"{[Key="YYYYYYYYYYYYYYYY"]}[Data],
    #"ZZZZZZZZZZZZZZZ" = #"YYYYYYYYYYYYYYYY"{[Key="ZZZZZZZZZZZZZZZ"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(#"ZZZZZZZZZZZZZZZ",{"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

If it doesnt work,let me know.
 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

[jbc22]

@v-kelly-msft 

This worked! Thanks so much. Interestingly, it seems to be opposite of what was suggested in the link which contained a blog about seperating the queries, which was what took me down the two query path origionally. Thanks for setting me straight!

[venal]

@jbc22 

Can you please share the sample data with the PBIX File.

BR

Venal.

[jbc22]

@venal The file itself is hard to anonymize since it has connection information to the two data sources.

All that is in the test file is Query1 which has four steps:

1. Source = Smartsheet.Tables(),

2. Navigation

3. Navigation

4. Navigation

and Query2 which is detailed in my origional post.

Please let me know if there are any other details that would be of help. Thanks.