Setting a variable from a SQL.Database call within the Advanced Editor

[JWolmanSymplr]

I am trying to figure out if this is possible but I'd like to retrieve the one row/one column from a SQL.database call and set a variable so I can use those results in another variable which I then will use as the basis for my OData.Feed call to Azure DevOps.

I will past the Advanced Editor information below. I'd like to know if I can somehow grab the results from the ReportingOptions = SQL.Database call (again this will be one row, one column) into a new variable which I then can use that in my BaseURL variable. The reason for this is so we do not have to hard code our ProjectNames in the Dataflow itself, but we can change them externally (when we add a new one) so the Dataflows will dynamically be updated if we add a new Portfolio in Azure DevOps.

let
ReportingOptions = Sql.Database("sql-server", "sql-database", [Query = "select OptionValue from My_Options where OptionName = 'Reporting Option'", CreateNavigationProperties = false]),
Options = [How can I get the result set from ReportOptions above - which is one row, one column into a variable so I can use that variable in the BaseURL variable?]
BaseURL = "https://analytics.dev.azure.com/xxx/_odata/v3.0-preview/WorkItems?$filter=(WorkItemType in ('Feature')) and (Project/ProjectName in (" & Options & "))&$select=WorkItemId,WorkItemType,Title&$expand=AssignedTo($select=UserName),CreatedBy($select=UserName),Area($select=AreaPath)",
Source = OData.Feed(BaseURL, null, [Implementation = "2.0"]),
#"Remove columns" = Table.RemoveColumns(Source, Table.ColumnsOfType(Source, {type table, type record, type list, type nullable binary, type binary, type function}))
in
#"Remove columns"

Thanks.

[JWolmanSymplr]

As doing something similar to this it technically works when you are editing the dataflow but when I try to save you get a failure to save by Power BI server and it does not let you save. I feel this might be due to SQL injection security reasons. Anytime I try to use an external retrieval of my options be it SQL Server or SharePoint, I get it to work within the dataflow editor but I can never Save & Close without an error.

[v-heq-msft]

Hi @JWolmanSymplr ,
According to your description, you can extract variable values from the table returned by the Sql.

let
    // Fetch the Reporting Option from SQL Database
    ReportingOptions = Sql.Database("sql-server", "sql-database", [Query = "select OptionValue from My_Options where OptionName = 'Reporting Option'"]),
    // Extract the first value from the result (assuming there's only one row)
    ReportingOptionValue = First(ReportingOptions)[OptionValue],

    // Define a variable with the extracted value
    Options = ReportingOptionValue,
    
    // Use the variable in the BaseURL
    BaseURL = "https://analytics.dev.azure.com/xxx/_odata/v3.0-preview/WorkItems?$filter=(WorkItemType in ('Feature')) and (Project/ProjectName in (" & Options & "))&$select=WorkItemId,WorkItemType,Title&$expand=AssignedTo($select=UserName),CreatedBy($select=UserName),Area($select=AreaPath)",
    Source = OData.Feed(BaseURL, null, [Implementation = "2.0"]),
    #"Remove columns" = Table.RemoveColumns(Source, Table.ColumnsOfType(Source, {type table, type record, type list, type nullable binary, type binary, type function}))
in
    #"Remove columns"

Note that the First function assumes that the SQL query returns only one row. If the query is likely to return more than one row, you'll need to decide how to handle it, perhaps using First and adding some additional logic to make sure you get the right value.

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly