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 ...
Need Help
Tutorial Requests
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.