Forum Discussion

RobSmith1988's avatar
RobSmith1988
New Member
4 years ago

Return results for multiple conditions sourced from query results

Hi all, 


New user of PowerBI and OData queries and am struggling to use a list of returned values from one OData query as a condition in another. 


The below OData query will return the top 100 records where the RuleID = 4:

 

<sourceURL>webAPI/Odata/v1/GetLatestExecutionResults(RuleId=@RuleId,EnvironmentId=@EnvironmentId,TopCount=@TopCount)?@RuleId=4&@EnvironmentId=1&@TopCount=100

 

However, there is a dynamic list of RuleIds and I require the top 100 records for each number of the list (as one output)

 

I have this query to create a list of values 

 

<sourceURL>webAPI/Odata/v1/Rules?$select=RuleId&$filter=RuleReportPropertyLink/any(link:(link/ReportProperty/Name eq 'PropertyFilter'

 

This returns a list of 1,2,3,4,5,8,9,11,14,29

 

Essentially, I want to use the numbers returned in the second query to replace the red number 4 in the first query. 

 

Apologies if this is poorly phrased, first time posting. 

 

Thanks in advance

Need Help
Tutorial Requests

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    4 years ago

    Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.  This starts with your list and shows how to convert it to a table, rename the column, convert it to type text, and then concatenate the value into your web call.

     

    let
    Source = {1,2,3,4,5,8,9,11,14,29},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "RuleID"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"RuleID", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Web.Contents("<sourceURL>webAPI/Odata/v1/GetLatestExecutionResults(RuleId=@RuleId,EnvironmentId=@EnvironmentId,TopCount=@TopCount)?@RuleId=" & [RuleID] & "&@EnvironmentId=1&@TopCount=100"))
in
    #"Added Custom"

     

    Pat

    • RobSmith1988's avatar
      RobSmith1988
      New Member
      4 years ago

      Hey Pat, 


      Thanks so much for taking the time to reply, really appreciate it!

       

      I have been able to run your query and it indeed returns a list with an additional column, which is a great start. I was hoping that I would be able to "loop" the the below query using each of the rows returned in the list in place of the red 4

       

      <sourceURL>webAPI/Odata/v1/GetLatestExecutionResults(RuleId=@RuleId,EnvironmentId=@EnvironmentId,TopCount=@TopCount)?@RuleId=4&@EnvironmentId=1&@TopCount=100

       

      This would then give me a table of results for the top 100 of each of the rules in that list. Is this a case of defining what information I would like returned from the "GetLatestExecutionResults" query or is another approach required?

       

      Thanks again, amazing stuff.