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
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
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
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!
Solved! Go to Solution.
@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
@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
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.
User | Count |
---|---|
158 | |
106 | |
96 | |
83 | |
75 |
User | Count |
---|---|
154 | |
137 | |
131 | |
81 | |
62 |