Forum Discussion

jbc22's avatar
jbc22
Frequent Visitor
6 years ago
Solved

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 ...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago
    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!
v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
6 years ago

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-not/td-p/18619

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
 

 

 

 

 

 

jbc22's avatar
jbc22
Frequent Visitor
6 years ago

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's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    6 years ago
    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's avatar
      jbc22
      Frequent Visitor
      6 years ago

      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!