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

Alex Wong

- 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. This is an updated template where the Power Automate flow does not require creating multiple per different data being gathered through Query API. The Power BI is build with 2 sets of data: all constituent assigned solicitor (fundraiser); all constituent no valid address and request no email.

- 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:

• Download: Power Automate Use By Power BI.zip
• Go to https://make.powerautomate.com/ > My flows
• Import flow by Import > Import Package (Legacy)
• While importing, you will receive some error as you do not have the same SharePoint List I have, You can ignore the error, and look somewhere at the top for a link to “Open the flow”, click on that.
• 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
• Save flow to get the URL to trigger the flow, copy it.
  • you must save the flow first before the URL is available on the trigger for you to copy
• Make sure to turn on the flow, imported file is generally “off” to begin with.
• Download: Power BI Using Query API.zip
• Unzip the file and open it in Power BI Desktop
• Go into Power Query for RE Query Data table by right click on one of the Data table on the right panel and select Edit query
• Click on FlowURL on the left panel, and replace the Current Value with the flow URL you copied.
• This Power BI comes with the Query JSON definition for getting Assigned Fundraiser and No Valid Address? and Requet No Email?
  • JSON Assigned Fundraiser
  • JSON NVA RNE
• Go to Assigned Fundraiser query, and click on the “gear” icon on the Source step
  

• Change the Site URL to the SharePoint URL that you created the SharePoint List for authentication key
• Click on the “gear” icon on the Navigation step
  

• Select the SharePoint List that has the authentication key
• Click on the “gear” icon on the Filtered Rows step
  

• Filter on the ID equals to the ID number of the record row that has the authenticate key
• Click on AuthKey step and update the formula bar with the column name of the authentication key. (I named mine “Access Token”, so change “Access Token” to your column name.
  

• Refresh Preview (this is going to take at least 30 seconds for Query API to run and get data)
  • ignore any error that you may see, and refresh again, if you still have error after 1 or 2 refresh, you will need help troubleshooting, then reply here.
• Repeart the above for the NVA RNE query if you want that, or delete the NVA RNE query if you don't want this data table.

Alex Wong

This post is to give you instruction on how to get a new query data into power bi.

You do not need to update the flow, it is capable of running any query definition and returning result to Power BI.

First you will need a Query JSON definition for the data you want to pull into Power BI

• Go to RE NXT > Query in webview
• Open an existing Query that you already did and want the output into Power BI
  • or create a new query, and config all the criteria, output, sort and option
• go to the Options tab, and click on Copy query JSON button

• Go to a JSON Editor, many available on the web, but I use this one: https://jsoneditoronline.org/
• Paste the copied Query JSON into the left panel
• Remove these JSON properties (if they are there, if you are not using a saved query, some of them wouldn't be there):
  • category_id
  • description
  • name
  • others_can_execute
  • others_can_modify
• Click on the icon that will Compact the JSON into ONE line of text

• Copy the now-compacted Query JSON
• Open the Power BI, and enter Power Query
• On the left panel: Queries, right click on an empty area and select New Parameter…

• Name your parameter (i.e. JSON My Query); Select Text as the Type; Paste the copied Query JSON in the Current Value field; (you can add a Description if you want, but not needed)

• Right-click on Assigned Fundraiser (or an existing query that is using Power Automate flow to run Query API for data) and select Duplicate

• Name your query to somethign meaningful for the data table (i.e. My Query)

• Click on SourceJSON step and go to the formula bar and click the down error to expand on it

• Replace where you see #"JSON Assigned Fundraiser" with the name of the parameter you created

• Click Refresh Preview icon at the top menu.

Scott Davies

@Alex Wong I am attempting to follow your instructions.

Sadly, I am stumbling at the first hurdle! I don't see any option to “Open the flow” in power Automate.

I clicked the import legacy flow option. Then I get this screen and there is NO option to “Edit the flow” or similar.

Any suggestions? Thank you!

Alex Wong

@Scott Davies
You need to click on the icon in the “ACTION” column and follow through to create the flow, and create the connection needed for the flow.

Scott Davies

@Alex Wong when I click the action (spanner) column, it gives me the option to create as new, which I do and then click save.

However, the import button at the bottom of the page is greyed out. It's obviously missing something.

Alex Wong

@Scott Davies
there are 3 more rows of Action icon you need to click on.

Scott Davies

@Alex Wong when I click on the action (spanner) icon, I get this:

Clicking on +Create new opens a new window with these options:

I don't see any option to use the connection or add it to the flow. How do I allocate one of these options to the flow?

Alex Wong

@Scott Davies
First order of business, Blackbaud connector are “premium” connectors, so you will need to be using a paid license of power automate (i.e. Power Automate per user license, for non profit, it should be $3 / month).

If you do have that, then for each “Related resources”, you will need to choose a connection, if a connection already exist, you should be able to just select it (i.e. Microsoft Teams and SharePoint), but for one that you never used before, you will need to click on “+Create new” then on next page, click on the “+ New connector” at the upper left of your 2nd screenshot.

then on the next screen, search for name of the connector you trying to create a connection for. “Blackbaud SKY Add-ins”, once found, click on the + on the RIGHT to login to your RE NXT environment.

Becky Griswold

@Alex Wong I'm attempting to access this flow and am getting the below error message which I can't get past. Any thoughts on what might be causing this? All the related resources appear to be connecting correctly, they have the green checkmark.

Alex Wong

@Becky Griswold
please show the full screen of the import.

if you setup all the connection, sharepoint will give you an error b/c you can't access my sharepoint list. you will then see near the top, somewhere that will say “Open Flow”. Click on that to update the SharePoint get item as per instruction

Becky Griswold

@Alex Wong Thank you! I was looking for a button or something similar. This morning I was trying again in order to get a full screenshot to post and found the “Save as a new flow” link within the Next Steps section. That seems to have gotten me into the flow to make the needed edits. Hopefully I won't need to bug you again I really appreciate your help and expertise.