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

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

• Right click on Expanded Column 1 step and Delete
• It will take some time to load data, then click on the icon to the right of Column1 and select the data field you want. Suggest to uncheck Use original column name as prefix and uncheck QRECID.

• Click Refresh Preview icon at the top menu.

@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
there are 3 more rows of Action icon you need to click on.

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

Thank you Rebecca. This is a good starting point.

I just took a look and the pbix file is the "first version" where you identified an issue that made it not work when published to power bi service (authkey in URL dynamically is not allowed in BI servcie).

But this will give me a good starting point instead of recreating the whole thing again.

@Kelly Hogan please wait before using the attachment Rebecca put on her post. I will update it and provide one that works in Power BI service.