If you need to automatically send tabular data from your Microsoft Power BI reporting dashboards to our secure SFTP server, Microsoft Power Automate is the most effective tool.
Because Power Automate and Power BI are both part of the Microsoft ecosystem, you can build this automated data pipeline without writing any custom code. This guide walks you through the exact steps to generate your data query, build the automated flow, and securely connect to our servers.
While this article will focus on extracting data from Power BI, you can easily adapt these steps to pull data from other sources, like OneDrive or email. This can allow you to integrate to multiple different systems using a similar workflow approach. See the Additional Integration Options section below for some suggested examples.
Prerequisites
Before you begin, ensure you have the following tools and permissions:
Create workflows and set up connections. Please note some connectors require a premium licence.
What Is SFTP?
SFTP (Secure File Transfer Protocol) is an industry-standard method for safely moving files between two systems over the internet. Instead of sending files across the open internet where they could be exposed, SFTP builds a temporary, heavily encrypted private tunnel between your network and ours, ensuring your sensitive data arrives safely.
Part 1: Tools and Initial Configuration
In this first video, we cover the essential tools required and how to complete the initial setup in Envisio and Power BI.
00:07: This tutorial explains how to set up an automated data integration between Microsoft
00:11: Power BI and Envisio. You will learn the necessary tools and initial configurations
00:16: to establish a secure and efficient data transfer process.
00:20: In part one, we'll be covering the tools that you'll need to successfully set up your
00:24: data integration, and we'll cover the configuration of the Envisio data source for
00:28: SFTP integration.
00:29: SFTP is a secure network protocol used to transfer and manage files over an
00:35: encrypted connection.
00:36: We will need access to three separate tools to set up our integration.
00:39: The first one is Envisio. This is where we will configure the data source to store the
00:43: performance data coming from Power BI.
00:49: The next tool is Power
00:50: BI. This is where we store and maintain the performance data that we're going to be
00:54: sending to Envisio.
00:55: The third tool we'll need access to is Microsoft Power
00:58: Automate.
00:59: This is the workflow automation tool we'll be using to collect the data from Power BI
01:04: and send that to Envisio on a preset cadence.
01:07: When you've got access to all of the tools and you're ready to start setting up your
01:10: integration,
01:11: we will start by going to Power BI.
01:14: From here we need to setup a table visual pre configured with the data
01:18: we are going to send to Envisio in an existing or new report.
01:21: In this example, I already have a report called HR data with a simple table visual
01:26: of annual HR data,
01:29: including a total employee count,
01:30: new hires, separations, and bad hires.
01:35: Once I've set up my table visual, I need to go into Envisio and configure my data
01:40: source to match that same format.
01:42: As you can see here, my Envisio data source has the same columns and data types as
01:47: the Power BI table visual.
01:49: After I've set up my data source in Envisio, I can start to configure the
01:53: integration options . To do
01:55: that, I'm going to click on the data integration option on the top right-hand corner of
01:59: the data source.
02:07: From here I'm going to click the SFTP tab and then enable the SFTP.
02:12: The first section is SFTP credentials.
02:14: We will be using these in a later step to connect to the SFTP.
02:28: Below is the option to append (adding rows to our data source), or replace (overwrite
02:33: all data. Below that, we can set a schedule of how often we want to check if an
02:38: updated file is available
02:39: and to sync that into Envisio. I have configured a weekly cadence to run every
02:44: Monday evening.
02:45: We can also control the notification for these data syncs.
02:47: The default is to notify you via email every time it runs.
02:52: You can also set this to only when there are errors, or to turn off email
02:55: notification altogether.
02:57: Once you've done that, you can go ahead and click save.
03:07: In part two, we will configure the automated workflow in Power Automate by collecting
03:11: the data from Power
03:12: BI, converting this to a CSV file, and then sending to Envisio.
Step-by-Step: Generating the Power BI Query & Setting up Your Envisio Data Source
You do not need to know how to write database queries. Power BI will generate the code for you using the Performance Analyzer.
Open Power BI and select the correct workspace.
Either create a new report, or edit an existing report.
Create a standard Table visual on the canvas. Add all the columns, categories, and metrics exactly as they should appear in your final export file. Apply any necessary filters.
In the top ribbon, click the View tab and select Performance Analyzer.
In the new pane on the right, click Start recording, then click Refresh visuals.
Find your Table visual in the log list, expand it (+), and click Copy query.
Paste this DAX code into a temporary Notepad file. You will need it in Part 2.
Setup a new data source in Envisio and match the same columns and data types to you Power BI Table visual.
Go to the integration options on your Envisio data source and enable SFTP.
Update the sync options and make a note of the SFTP credentials. You will need these in Part 2.
Part 2: Workflow Setup and Testing
This second video walks you through building the actual automation, formatting the data, and successfully connecting to the SFTP server.
00:07: This tutorial explains how to set up an automated data integration between Microsoft
00:11: Power BI and Envisio. You will learn how to set up the workflow, configure
00:15: connections, and verify successful data synchronization.
00:20: Before we can create our automated workflow, we need to complete two steps.
00:25: The first step is to collect the SFTP credentials from the Envisio data source.
00:29: These can be found by clicking on the SFTP credentials area. We'll be using these in
00:34: a later step to connect to the SFTP.
00:36: The next step is to open the Power BI report with the table visual from part 1.
00:41: First, edit the report.
00:43: Then click view, and open the performance
00:45: analyzer.
00:47: Under the new performance analyzer section, click start recording and then refresh
00:51: visuals.
00:59: This will return a list of all the visuals in your report.
01:03: In this case, we only have the one table visual.
01:06: Click on the table visual in the results and then copy the DAX query.
01:11: This is going to be used when setting up the automated workflow.
01:14: With the DAX query added to the clipboard, navigate to Microsoft Power Automate to
01:19: start creating the new workflow.
01:22: Click on Create, and select scheduled cloud
01:26: flow. Give the flow a name, and then set up the
01:29: recurrence. I'm going to set mine to run every day at 10
01:32: a.m.. Finally, click create to go to the workflow editor
01:40: To start adding actions to the workflow
01:42: select the plus button under recurrence. Scroll through the available connectors and
01:47: select Power BI, then the option to run a query against a dataset.
01:58: At this point, you may be asked to sign in to your Power BI
02:01: account. This is a one-time connection between Power Automate and
02:05: Power BI. Any new workflows will be able to use this connection to your Power BI workspace.
02:11: Then select the workspace, and data set from Power BI.
02:15: Paste
02:15: the DAX query from the previous step into the Query Text field.
02:22: Next add a new action to our workflow,
02:24: this will convert the data query from Power
02:27: BI into a CSV file format.
02:30: Select Data Operation from the built-in tools, then select Create CSV Table.
02:36: Under the From field
02:37: use the forward slash to select Insert Dynamic Content.
02:40: Then select the option for first table rows from our previous action.
02:53: Add a fourth and final action to the workflow,
02:55: this will connect and send our CSV to the Envisio SFTP location.
03:02: Search for SFTP and select the 'create file' option.
03:07: When connecting for the first time we need to add our connection details from the
03:10: Envisio data source.
03:12: After the SFTP connection has been created we can use it for other workflows and Envisio
03:17: data sources .
03:19: Copy and paste the details from the Envisio data source,
03:22: starting with the internal name for the connection, host server address, then
03:26: username and password.
03:29: If using a username and password to connect we can ignore the SSH key fields. Enter
03:34: 22 for the port number. and make sure to disable the SSH host
03:38: key validation. The final step is to enter the information for the root path folder.
03:44: The host server address is called 'host URL' in Envisio, this is the same for all
03:49: connections and does not change. The user name and password is unique to
03:53: you, if you are using single sign on you will need to use the
03:57: 'Generate Secondary Password' option.
03:59: All root folder paths start with envisio dash pav2 dash sftp dash production, followed
04:05: by your Envisio
04:06: subdomain, then the first part of your username from the data
04:09: source, this is the text before the underscore. Remember to disable SSH host
04:14: key validation when authenticating using a username and password, and don't forget
04:18: to add the port number.
04:31: After creating the SFTP connection, enter the Folder Path for this data source. This
04:36: should be the same as the root folder path plus the data source ID.
04:42: Enter the file name,
04:42: for example hrdata.csv. Power automate
04:46: can also use dynamic file names to include the day, month, and year if preferred.
04:50: preferred
04:52: For the file content, use dynamic content to select the CSV output from our previous
04:57: step.
05:03: Then save the completed workflow.
05:07: If you see a green "your flow is ready to go"
05:10: message at the top, we can go ahead and run a test.
05:16: Select the test option and then the manually option. Selecting run flow will trigger
05:20: each action and send a file to the Envisio SFTP location.
05:37: This may take a minute or two.
05:39: You should see a success message to confirm that the workflow has run correctly.
05:50: As you can see all actions in our flow ran successfully.
05:54: To finish testing, navigate back to the Envisio data source to run a manual sync
06:00: Under the SFTP section select the manual sync option,
06:03: this runs the SFTP sync process on demand, instead of waiting for the next scheduled
06:08: sync.
06:11: This may take a minute or two.
06:14: After seeing the processing message, you should see it turn green with a success
06:17: message.
06:19: We can now see that the sync has completed successfully.
06:23: To confirm the data has updated close the integration options area and check that
06:27: you are seeing the latest data in the data source.
06:30: Congratulations on setting up your first Power BI integration!
06:34: Power Automate offers lots of different connectors to use for different systems and
06:38: workflows
06:38: you can use. Here are some examples. Using a trigger for attached files sent to an
06:43: inbox, well suited for legacy systems that have limited integration options.
06:48: Checking for files added to OneDrive or SharePoint, or a scheduled send of an
06:52: existing file.
06:54: Or connecting directly to an existing database to send data to Envisio. Please
06:58: feel free to reach out to support@envisio.com if you have
07:01: any questions.
Step-by-Step: Building the Power Automate Flow
Create the Trigger: Log into Power Automate and create a Scheduled cloud flow. Set the recurrence to your preferred schedule (e.g., daily at 2:00 AM).
Query the Data: Add a new step and select Run a query against a dataset (under the Power BI connector).
Select your Workspace and Dataset.
Paste the DAX code you copied in Part 1 into the Query text box.
Format to CSV: Add a new step and select Create CSV table.
Click inside the From field and select First table rows from the Dynamic Content menu.
Configure the SFTP Connection: Add a new step, search for the SFTP-SSH connector, and choose Create file. If this is your first time using this connector, you will be prompted to enter your credentials:
Connection Name: (e.g., Envisio_SFTP)
SSH Server Address: Enter the exact address from the data source, this is called Host URL in Envisio s-250e2d675aae4f07a.server.transfer.us-east-1.amazonaws.com
Username & Password/Key: Enter your provided credentials.
Disable SSH Host Key Validation: Set this to Yes to ensure Power Automate does not reject the connection.
Route folder path: /envisio-pav2-sftp-production/{Envisio URL subdomain}/{sftp username} (For Canadian instances use /envisio-pav2-sftp-productionca, for Australian Instances use /envisio-pav2-sftp-productionau
Map the File Details:
Folder Path: This is the same as the root folder path plus the data source ID. (e.g., /envisio-pav2-sftp-production/bayviewinc/rmillw/175121)
File Name: Type your desired file name (e.g., Daily_Export.csv).
File Content: Select Output from the "Create CSV table" step in the Dynamic Content menu.
Save and test your flow!
00:07: This tutorial shows you how to automate data extraction from Power
00:10: BI using Power Automate. You will learn to create a scheduled workflow that prepares
00:15: data for sending to Invisio.
00:17: Before we can set up our automated workflow using Power Automate, we need to
00:21: identify the data in Power BI that we want to send to Invisio.
00:31: To do this, we need to start by going to the report in Power BI.
00:36: As you can see here, I have my report called HR Data with my simple table visual.
00:47: To be able to collect the correct query that we will use with Power Automate
00:50: in the next step, I need to edit my report, click the view option, and then open the
00:55: performance analyzer. From here,
00:57: I can start the recording under the performance analyzer section and then refresh
01:02: the visuals.
01:15: From here I will see a list of all of the visuals that are in my report.
01:18: In this case I only have my one table visual which I can click on here.
01:22: What I need from this section is to copy the DAX query. This is what we will use
01:27: with Power Automate to be able to identify the correct set of data that we want to
01:31: send to Invisio. By copying it,
01:33: it now adds this to my clipboard for us to add to Power Automate.
01:57: We can now go over to Power Automate and we're going to start building our automated
02:01: workflow.
02:02: To do that, you can just click Create.
02:06: There are a number of options in Power Automate for how your workflow is triggered.
02:11: In this example, I'm going to do a nice, easy, scheduled cloud flow by clicking here.
02:21: Choose when and how often the flow runs by selecting the appropriate option.
02:26: I then need to give my flow a name.
02:28: In this case, I will call it HR data Invisio.
02:31: I can then set the recurrence of when to run this flow.
02:41: I want to run it every morning at 9 a.m. So I need to change the recurrence from
02:45: minute to
02:47: day. This will now run every day at 9 a.m.
02:57: I can then click create. I am now brought to the workflow editor area.
03:01: This is where I can configure additional steps to add to my automated workflow.
03:15: Add a new step to your workflow below the recurrence
03:17: we have just set by clicking the plus button.
03:21: The first step is to add a new step to my workflow below the recurrence
03:24: we have just set.
03:27: To do that,
03:27: I can hit this plus button here.
03:30: Our first action will be to use the Power BI connector.
03:33: I can search for that in the top here.
03:42: And I want to make sure I see all of the options for Power BI.
03:47: From here, I want to use the run a query against a dataset option.
03:51: At this step, you may be required
04:02: To sign in to your Power BI account to provide a connection between Power BI and
04:06: Power Automate.
04:12: Once you've signed in, you should be able to select the workspace that has the data
04:16: we're looking to collect and send.
04:19: Then we need to collect the correct data set where the report is available.
04:22: In this case, it's in my human resources data
04:25: set. I can then paste in the query that we took from
04:28: Power BI in the previous step into the query text box.
04:42: From here, I don't need to make any other changes or updates.
04:45: I can then go ahead and make a new step on our workflow.
04:51: Our next step in our workflow is to create a CSV file output from our query against
04:56: the Power BI dataset.
04:58: To do this, I need to select the Data Operation option and create CSV table.
05:09: Under the From field,
05:10: I can use the Dynamic Values to select the First Table Rows option from the previous
05:14: step.
05:19: We now have a workflow that has a daily reoccurrence to trigger.
05:22: This will then run the query against our data set in
05:25: Power BI to collect the same data from our table visual.
05:36: And then it will create a CSV table.
05:39: At this point, we can go ahead and save our workflow.
05:45: To make sure that there are no errors and there are no issues.
05:49: In part 3, we'll be going through the next step in the workflow to connect to the
05:53: Invisio SFTP
05:54: and physically sending the CSV file to Invisio.
06:01: Thank you.
Additional Integration Options
While this guide uses Power BI as our primary data source, the true engine behind this automation is Power Automate. The hardest part of this process—establishing the secure SFTP connection—is already complete.
Now that your secure pipeline is built, you can treat Power Automate like a plug-and-play system. If your business processes require extracting data from a different location, you can simply swap out the Power BI step for a different trigger. Here are a few highly effective ways to adapt this exact workflow to handle other common data sources:
The Email Pipeline (Office 365 Outlook): Best suited for legacy systems or third-party vendors that deliver data via email. You can use the "When a new email arrives" trigger to automatically extract CSV or Excel attachments from your inbox and route them directly to the SFTP server.
The Shared Folder Route (SharePoint / OneDrive): Ideal for collaborative teams or manual data drops. You can configure this in two distinct ways:
Automated: Use the "When a file is created" trigger to instantly send a file the moment a user drops it into a specific, designated folder.
Scheduled: Use a "Recurrence" trigger combined with the "Get file content" action to automatically pull and send a continuously updated file at the exact same time every day.
The Raw Database Export (SQL Server): Perfect for direct, system-to-system data transfers that bypass reporting dashboards entirely. You can use the SQL Server connector to run an "Execute a SQL query" action on a schedule, automatically convert the output to a CSV, and forward the results to our server.
Explore Endless Possibilities: Power Automate supports hundreds of native integrations. Whether your data lives in Salesforce, Google Drive, or a custom API, there is likely a pre-built way to connect it. Browse the full Microsoft Power Automate Connector Reference library to see what else you can build. Please note some connectors require a premium power automate subscription.
Comments
0 comments
Article is closed for comments.