Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Using Column from one query as argument for another query

HI All, 

 

I am very new to PowerBI and I am having trouble constructing a query or function to do the following:

 

1. I have an OLE DB Query that returns two columns. The columns are 'ID' and 'Name'.   This query returns about 300 rows.  This works fine and returns data as expected:   

 

let
Source = OleDb.DataSource("provider=PIOLEDBENT.1;initial catalog=ASC-Dev;data source=fth-dev-pib", [Query="Select ea.ID, ea.Name#(lf)FROM [ASC-Dev].[Asset].[ElementAttribute] ea#(lf)Where ea.ElementID = '58ac1d82-96a6-11e9-bef2-54ee75acd4e0'"])
in
Source

2019-07-10 15_56_53-Untitled - Power Query Editor.png

2. I want to use the results in the ID column as the argument for a second query, that will return multiple rows of data for each ID.  Basically, each ID represents a machine sensor, such as pressure or temperature, and there are multiple readings over time for each.  I have it working by hard coding the sensor ID in as shown here:

let
Source = OleDb.DataSource("provider=PIOLEDBENT.1;initial catalog=ASC-Dev;data source=fth-dev-pib", [Query="SELECT a.Time, a.Value#(lf)FROM [ASC-DEV].[Data].[Archive] a#(lf)WHERE a.ElementAttributeID = '000000a3-0000-0000-1ef4-ffffffffffff'"])
in
Source

2019-07-10 15_58_12-Untitled - Power Query Editor.png

 

3. How can I loop through or dynamically pass the rows from the ID field in the first query to be used as arguments in the second query?  Basically I'd like to dynamically fill this prameter:

WHERE a.ElementAttributeID = '000000a3-0000-0000-1ef4-ffffffffffff'

 

Thanks in advance for any advice!  

 

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@Anonymous 

 

You may just select Invoke Custom Function in Query Editor.

http://community.powerbi.com/t5/Desktop/Create-tables-based-on-all-Query-Parameters/td-p/345947

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-chuncz-msft
Community Support
Community Support

@Anonymous 

 

You may just select Invoke Custom Function in Query Editor.

http://community.powerbi.com/t5/Desktop/Create-tables-based-on-all-Query-Parameters/td-p/345947

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.