Sharing my Power Automate and Power BI template: Power BI use of Query API via Power Automate

Alex Wong

NOTE: A New Template has been posted as a new post: https://community.blackbaud.com/forums/viewtopic/586/71846

New Template makes it much more user friendly and include instruction on how to get new query data into your Power BI. This template/post will be kept here for historical purpose and for those that still want to use this method (one flow for one query) of getting data into Power BI.

=======================================================

- Description: Use of Power Automate HTTP request received trigger to run Query API to gather RE data that is not easily obtainable using SKY API endpoints. Example gather data for all constituent assigned solicitor (fundraiser). Can be modified to get other data point that isn't available via “list” API endpoint. (i.e. no valid address, request no email, etc)

- Required connections: Blackbaud SKY Add-ins, SharePoint, Microsoft Teams

- Suggested skill level required to use this template: Intermediate

- Configuration details:

• Create a SharePoint List that store the authentication key to be used for authenticated use of the Power Automate flow
  • this authentication key should be at least 32 characters in length, with only alphanumeric characters only. You can use any encoding tool, or just type a bunch of random characters and numbers.

- Category: Automation, Data Management, Power Query

- Instructions:

• Import flow: BBQueryAPIDataForPowerBI_20241218061220.zip
• Update flow
  • Get SharePoint List Item for Auth Key
    • update the SharePoint site, list name, and id to get the authentication key
  • Condition Auth Key Not Match
    • update right side of condition by removing the dynamic content there, and then replace it with the dynamic content obtained from Get SharePoint List Item for Auth Key action's auth key
  • Compose Query JSON
    • if you want, update JSON that queries for the data you want, or uses what's there now for assigned solicitors info
• Save flow to get the URL to trigger the flow, copy it.
• Open Power BI file by unzipping: Power Automate Query API.zip
• Go into Power Query for RE Query Data table
  • Update FlowURL Parameter with the copied URL from the flow
  • Refresh Preview (this is going to take at least 30 seconds for Query API to run and get data)

Alex Wong

If you want data for No Valid Address? and Request No Email? copy the following JSON, and replace the content of the Compose Query JSON action. Will post more JSON in future for other data that may be useful.

{
"select_fields": [
{
"query_field_id": 349,
"user_alias": "id"
},
{
"query_field_id": 601,
"user_alias": "nva"
},
{
"query_field_id": 101488,
"user_alias": "rne"
}
],
"filter_fields": [],
"sort_fields": [],
"gift_processing_options": {
"soft_credit_option": "Donor",
"matching_gift_credit_option": "MatchingGiftCompany",
"use_gross_amount_for_covenants": false
},
"advanced_processing_options": {
"use_alternate_sql_code_table_fields": false,
"use_alternate_sql_multiple_attributes": false
},
"type_id": 18,
"format": "Dynamic",
"suppress_duplicates": false,
"constituent_filters": {
"include_inactive": true,
"include_deceased": true,
"include_no_valid_addresses": true
}
}

Rebecca Sundquist

@Alex Wong, I love that you included SharePoint to check the user's authentication on the flow with an HTTP request set to run by “Anyone.” But I didn't expect Power BI to refuse to refresh online when a URL parameter is dynamic: Data refresh in Power BI - Power BI | Microsoft Learn

Users need to be aware of the refresh limitation on Power BI Service, but also conscious of protecting authentication. Forcing SharePoint authentication to access the authKey in Power Query ensures the right users are gaining access to the flow, since Power Automate isn't using oAuth in a manual HTTP request trigger.

I haven't found a secure solution for online refreshes leveraging the PA flow. I'm interested in what other users might be trying. We can discuss in our Power BI user group tomorrow!

Alex Wong

@Rebecca Sundquist
I don't remember if I tried refreshing the BI when it's on power bi service. I will try and report back

Alex Wong

@Rebecca Sundquist
Yup, you are right, can't dynamically construct a URL as datasource for refresh on Power BI service.

However, there is promise:

In most cases, Power BI semantic models that use dynamic data sources can't be refreshed in the Power BI service. There are a few exceptions in which dynamic data sources can be refreshed in the Power BI service, such as when using the RelativePath and Query options with the Web.Contents M function. Queries that reference Power Query parameters can also be refreshed.

So the authkey is a URL parameter that can be passed in as “Query”. I will try that and post new template if all works.

Thanks for finding this issue.

Alex Wong

@Rebecca Sundquist
I have updated the zip file with the pbix file with the updated M code in Power Query that will allow for dynamic authkey to be pass in via Query parameter of the Web.Content() function.

SourceJSON = Json.Document(
Web.Contents(
FlowURL,
[
Query = [authkey = AuthKey]
]
)
)

Erik Leaver

@Alex Wong Adding the recording of your demonstration from the Jan 7 Power BI User group for people to reference as well:

Alex Wong

@Erik Leaver
Can you help me make an update to the template showcase link to point to the new/more versatile template?