Sharing my Power Automate Flow: Gateway free Power BI data refreshes

Options
Dan Snyder
Dan Snyder
Ninth Anniversary Kudos 5 PowerUp Challenge #3 Gift Management Name Dropper
in SKY Developer General Discussions

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

CSVtoXLSXEncodian_20221010173338.zip

ClearoutSharePoint_20221010174056.zip

Tagged:

Comments

  • Heather McLean
    Heather McLean
    Eighth Anniversary Kudos 3 Name Dropper Participant

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

  • Hallie Guiseppe
    Hallie Guiseppe
    Fifth Anniversary Kudos 4 Name Dropper Participant

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

  • Dan Snyder
    Dan Snyder
    Ninth Anniversary Kudos 5 PowerUp Challenge #3 Gift Management Name Dropper

    @Hallie Guiseppe We do not use split gifts, sorry.

  • Carol Grant
    Carol Grant
    Seventh Anniversary Kudos 3 Name Dropper Participant

    @Dan Snyder- Hi Dan, how did you get around the FTP Connection issue where it is hard to connect, it fails many times.

  • Dan Snyder
    Dan Snyder
    Ninth Anniversary Kudos 5 PowerUp Challenge #3 Gift Management Name Dropper

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

  • Keturah Bardouille
    Keturah Bardouille
    Ninth Anniversary Name Dropper Facilitator 1 Loyalty Badge

    @Dan Snyder
    Thanks for sharing

  • Keturah Bardouille
    Keturah Bardouille
    Ninth Anniversary Name Dropper Facilitator 1 Loyalty Badge

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

  • Dan Snyder
    Dan Snyder
    Ninth Anniversary Kudos 5 PowerUp Challenge #3 Gift Management Name Dropper

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


  • Wesley Ardoin
    Wesley Ardoin
    Name Dropper First Anniversary Facilitator 1

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

  • Dan Snyder
    Dan Snyder
    Ninth Anniversary Kudos 5 PowerUp Challenge #3 Gift Management Name Dropper

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

  • Wesley Ardoin
    Wesley Ardoin
    Name Dropper First Anniversary Facilitator 1

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

  • Dan Snyder
    Dan Snyder
    Ninth Anniversary Kudos 5 PowerUp Challenge #3 Gift Management Name Dropper

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

  • Wesley Ardoin
    Wesley Ardoin
    Name Dropper First Anniversary Facilitator 1

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

Categories