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!
v-kelly-msft
Community Support
Community Supportlet
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
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!
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
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!