Sharing my Power Automate Flow: Gateway free Power BI data refreshes
Look Ma, No Gateway!
For my birthday two years ago, the good folks at RADACAD posted a video called Change Excel Source in Power BI to OneDrive for Business – No Gateway Needed. While intrigued by the concept of not needing a gateway we are a Google shop so I was not quite familiar with OneDrive or how to regularly get my data there. So like my children at Christmas, I moved on from this shiny new idea until #bbdevdays in June when as an early anniversary present (everyone is so thoughtful) Jeff Austin presented a way to move your files from the BB hosted files folder to SharePoint and then refresh your Power BI datasets. Out of milestones for others to present me with solutions to my problems, I was left to my own devices and finally, I have something to share with you all.
Summary: This is an automated process to refresh your Power BI datasets using CSV and XLSX files and no gateway. Using Queue to export data files overnight, Power Automate is able to move those files to SharePoint for use as a data source in Power BI.
Required Connections:
- Queue Module
- Encodian - Don't want to pay for a subscription, check out Alex Wong's post
- SFTP-SSH - to access hosted files
- SharePoint
- PowerBI
Skill Level:
Intermediate
Flow Notes:
Flow #1 - Moving files from the hosted folder to SharePoint: When Queue exports a file into the Tenent File Share (aka hosted files) the flow is triggered and moves the file into SharePoint. The file is then deleted from the hosted files. It is important that the files are deleted, otherwise when a second file is exported to that folder, the flow will be triggered again and will try to move two files, one of which will produce an error since it is already in SharePoint.
Flow #2 - Convert file from CSV to XLSX: When we lost the ability to export to XLSX format, I changed most of my data source files to CSV for my Power BI reports, however, there were one or two that became too big and refreshes were failing. This flow uses Encodian because we already have a subscription, but you can use @Alex Wong 's solution for free!
Flow #3 - Clearing out SharePoint: As I mentioned in Flow #1, if you try to move a file into SharePoint with the same file name it will give you an error. Therefore, in the dark of each early morning this flow will run to clear out the SharePoint folder to make room for the new files and new data.
Power BI data source and configuration: I am not going to reinvent the wheel here as the RADACAD video and instructions do a great job explaining this, however, I did run into an issue of not being able to get the link in the manner they showed. What I did was went to the SharePoint site where the folder was with all the source files, copied the link at that page and then adjusted it to point to a specific file. What I ended up with was something like this:
https://ORGNAME.sharepoint.com/sites/SITENAME/Shared Documents/Power BI/Power BI 10 Year Gifts.csv
The ORGNAME and SITENAME will be pretty clear for your instance of SharePoint. The next two are navigating to the Documents area (mine is Shared Documents for some reason) and then the specific folder (Power BI for me) where all the files will land, followed by the filename and extension of course.
Happy Reporting!
HostedFilestoSharePoint_20221010173217.zip
Comments
-
@Dan Snyder - do you think this is appropriate to share at Power BI user group today, or is this more suited to Power Automate later in the month? Would you be willing to share it?
0 -
@Dan Snyder Does your org use split gift functionality? If so, how do you manage that in the export?
I've not had success exporting the split information in a format that I can use effectively. That is why I am using the PowerBi Connector. It enables me to bring the split amounts into my gift table, which allows me the flexibility to create accurate dashboards for my team. I only finished doing this last week so I'm not sure how well the report will refresh and so on, but getting the gift split information was my first obstacle and the reason why I am using the connector rather than exports.
0 -
@Hallie Guiseppe We do not use split gifts, sorry.
0 -
@Dan Snyder- Hi Dan, how did you get around the FTP Connection issue where it is hard to connect, it fails many times.
0 -
@Carolyn Grant
Once I was lucky enough to get the connector to save and be listed as valid I tried not to edit that portion of the flow! Unfortunately, the issues with the SFTP-SSH connector still seem to remain. Not really sure if it is a Microsoft issue with the connector or some validation thing with the hosted files.0 -
@Dan Snyder
Thanks for sharing1 -
@Dan Snyder I'm having so major issues with sftp-SSH all of my flows are failing because of bad gateway error. Any advice?
I've tried over and over to create a new connection but nothing works. The funny thing is one of the flows i have linked to the same gateway worked on a manual trigger but none of my scheduled file downloads work anymore since BB password refreshed for October.
0 -
@Keturah Bardouille You will need to enter the new password in your main SFTP connection (Data - Connections - SFTP Connection) when the automatic updates happen, but I know there is another thread about issues with the connection here.
0 -
@Dan Snyder
Hi Dan, how do you get queue to export a refreshed data set? The queries always revert back to static when scheduled in queue. Thanks for your help!0 -
@Wesley Ardoin You are able to use queue with dynamic queries attached to exports and for a couple I use dynamic queries and the output directly from the query.
Maybe I am not understanding your question? Happy to try to help, but I might need some more information.
0 -
@Dan Snyder
Thank you for your response, Dan. This article explains what is happening. I think perhaps if I skip the “refresh” step and just include the query-export step in the queue, the query will remain dynamic. I'm new to this and thought I had to refresh the query as a first step. Hope that makes some sense. Looking forward to hearing your thoughts. Thanks!0 -
@Wesley Ardoin Thanks for sharing that link. So in queue you can refresh queries which is what that KB article is talking about, but if queries are dynamic you do not need to do that. For example if you have a Gift Export with a dynamic gift query attached, you do not need to physically refresh the query (or have queue refresh it) for it to grab updated information from day to day/week to week, whatever the cadence.
I have our main queues set up to run each weekday in the morning and if I enter a gift on Monday, it will be available in Tuesdays export without touching anything. I hope that makes sense, but if you want to chat more feel free to send me a DM and we can set up a time to connect.
0 -
@Dan Snyder
Perfect. Yes, that makes sense. It didn't make sense to me that a dynamic query would need to be refreshed in the first place but the BB documentation did not explain that well. Thanks for your clarification!0 -
@Dan Snyder These instructions have been awsome, thanks. I have a question (I may missing some foundational knowledge, as our division is a new Blackbaud RENXT customer): Is there a way to move the files from Sharepoint to a local file server, using Power Automate, without the gateway? If not, do most institutions, who use the Power Platform, have the Gateway installed for tasks like this? I'm at a higher ed institution and this is our first project with Power Automate, but we don't have the on-prem gateway installed. I'm just curious about how much use the on-prem gateway would be beyond this task of moving files from the tenant share ultimately to our local server, which I can probably do another way but would prefer to move in the direction of using Power Automate for things that make sense.
0 -
@Lana Sain I am sure there is a way or a method of getting the data to a local file server, but I do not have experience doing that so could not provide guidance there. I have only used a personal gateway which has the limitations of being tied to a specific user/machine as the name implies.
@Alex Wong @Carol Grant not sure if either of you have experience with the on-prem gateway?
1 -
@Lana Sain Power Automate is a cloud service, so it can only work with cloud storage. The only other option that's still Power Automate would be Power Automate Desktop, which would still require a dedicated computer to run automation "as-if" a human did some interaction on browser and downloading to local computer is then possible.
An option around this is using OneDrive. If you are using Power Automate with Blackbaud, you will have to have a Microsoft licensed accounted with a minimum of Power Automate per-user license. By default, means you have a MS account with access to some amount of cloud storage space on OneDrive. OneDrive "client" app can be installed on the local machine, which can be config to auto-sync from OneDrive folder to the local file system.
I don't really recommend doing this though as there are some limitations.
Maybe you can start with what you are trying to achieve, I can probably suggest a different path that may make more sense.
0 -
@Alex Wong we have a report being exported, via the Queue, to the hosted files section in Raiser's Edge, and we just want to automatically move those each night a local file server without having to do it manually. Our BB consultant told us we could do this with Power Automate, but I am open to whatever is best practice. Your advice is greatly appreciated.
0 -
what do you do with the file that is stored locally?
also by "report being exported", can you be specific as to what you are running
0 -
@Alex Wong it is a constituent list (csv file) with various types of biographical and giving information. Most of this can be obtained in RENXT in one way or another but this list fulfills a particular type of daily need. In time, I think we can do away with it, but as of right now, while we are making the transition to RENXT, this list needs to be stored locally. I just need an automated process to get it from the hosted site to our local server.
0 -
you mentioned Queue (db view only feature) and constituent list (webview only feature). So I'm going to assume you meant either Query module export or Export module export in dbview.
Here are 2 options:
- Keeping RE:Queue to export, create Power Automate that trigger on sFTP file change and then download the csv file and upload into OneDrive or SharePoint location (cloud based location). On your location computer, OneDrive sync the folder location of the OneDrive/Sharepoint so it "appears" local to your computer.
- most likely to reflect what you have today
- sFTP trigger can be on/off some times, so not best options for future proofing your automation.
- Use Power Automate flow that triggers on the same schedule you have today of when your RE:Queue does the export. Then run the Query that you saved (assuming your RE:Queue runs a Query export) or modify a Query output that resemble your RE:Queue Export module export. Run the Query and save it to OneDrive/SharePoint location. Same as above to sync OneDrive to your local computer
- better option for future proofing your automation
- depending on how you plan to use the csv, we can automation how you plan to use the csv within the same flow and not needing to do any manual work. For example, automate making the csv into excel file and formatting it properly (nicely) and send it as an email to people that should see it every morning/week.
There are templates and tutorial on Community for both methods. I have done sFTP option previously and the Query API option. If you find that you'd like a helping hand to get things set up more quickly or avoid some of the common trial-and-error, I do offer hands-on training sessions and consulting hours. Sometimes a short working session can significantly speed things up and give you a solid foundation to build from. Either way, feel free to keep me posted on how it goes.
0 - Keeping RE:Queue to export, create Power Automate that trigger on sFTP file change and then download the csv file and upload into OneDrive or SharePoint location (cloud based location). On your location computer, OneDrive sync the folder location of the OneDrive/Sharepoint so it "appears" local to your computer.
Categories
- All Categories
- 6 Blackbaud Community Help
- 213 bbcon®
- 1.4K Blackbaud Altru®
- 402 Blackbaud Award Management™ and Blackbaud Stewardship Management™
- 1.1K Blackbaud CRM™ and Blackbaud Internet Solutions™
- 15 donorCentrics®
- 360 Blackbaud eTapestry®
- 2.6K Blackbaud Financial Edge NXT®
- 655 Blackbaud Grantmaking™
- 576 Blackbaud Education Management Solutions for Higher Education
- 3.2K Blackbaud Education Management Solutions for K-12 Schools
- 939 Blackbaud Luminate Online® and Blackbaud TeamRaiser®
- 84 JustGiving® from Blackbaud®
- 6.6K Blackbaud Raiser's Edge NXT®
- 3.7K SKY Developer
- 248 ResearchPoint™
- 119 Blackbaud Tuition Management™
- 165 Organizational Best Practices
- 241 Member Lounge (Just for Fun)
- 34 Blackbaud Community Challenges
- 34 PowerUp Challenges
- 3 (Open) PowerUp Challenge: Chat for Blackbaud AI
- 3 (Closed) PowerUp Challenge: Data Health
- 3 (Closed) Raiser's Edge NXT PowerUp Challenge: Product Update Briefing
- 3 (Closed) Raiser's Edge NXT PowerUp Challenge: Standard Reports+
- 3 (Closed) Raiser's Edge NXT PowerUp Challenge: Email Marketing
- 3 (Closed) Raiser's Edge NXT PowerUp Challenge: Gift Management
- 4 (Closed) Raiser's Edge NXT PowerUp Challenge: Event Management
- 3 (Closed) Raiser's Edge NXT PowerUp Challenge: Home Page
- 4 (Closed) Raiser's Edge NXT PowerUp Challenge: Standard Reports
- 4 (Closed) Raiser's Edge NXT PowerUp Challenge: Query
- 792 Community News
- 2.9K Jobs Board
- 53 Blackbaud SKY® Reporting Announcements
- 47 Blackbaud CRM Higher Ed Product Advisory Group (HE PAG)
- 19 Blackbaud CRM Product Advisory Group (BBCRM PAG)