Forum Discussion
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 ...
- 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
SQLIf it doesnt work,let me know.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
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.