SFTP/FTP files access

Power BI does not have a built-in feature to connect natively to SFTP servers. However, you can use a 3rd party connector, a custom data connector or other methods such as downloading files to your local machine or using a script to import the data from the SFTP server. It's worth noting that while Power BI doesn't have a built-in SFTP connector, it does have a variety of built-in connectors and data import options that allow you to connect to different types of data sources, such as Python.

You can use Python to write a script to connect to an SFTP server and import the data. Python has several libraries that can be used to interact with SFTP servers, such as Paramiko, pysftp, and SSH.NET. These libraries provide the necessary functions and methods to connect to the SFTP server, authenticate, and retrieve the data.

Here is an example of how you can use the 'pysftp' library to connect to an SFTP server using Password authentication. It's also worth noting that, you may need to have python and the related libraries installed on your machine, and the script needs to be executed by a user who has the permissions to access the SFTP server.

In this example I'm connecting to an SFTP server with multiple CSV files updated daily, I'm using the 'csv' library to read, parse and append the CSV data and use 'Pandas' library to work with the data in a tabular format. I'm also using "io" library to read the CSV binary data and hand it over to "pandas" library in order to parse it as tabular. I'm also using "os" library to get the file names and merge it with the data.

With this code you do not need to download a local copy of the data or stage it, all will be handled in the Power Query buffer. It also supports data refreshes in the service, you need to have data gateway installed and configured in "personal" mode.

From "Power Query Editor" -> New Source -> Python Script.

import pysftp
import pandas as pd
from io import BytesIO
import os

# Parameters to change
server_ip = 'YOUR SERVER IP OR URL'
server_port = PORT NUMBER #for sftp use 22
user_name='YOUR USER NAME'
server_password='YOUR PASSWORD'
remote_directory = '/path/to/data/directory'


# Connect to SFTP server
with pysftp.Connection(server_ip, port=server_port, username=user_name, password=server_password) as sftp:
    
    # Get the files under remote directory
    files = sftp.listdir(remote_directory)
    data_files = [file for file in files if file.endswith('.csv')]
    
    # Download each file as binary and append
    content = []
    for file in data_files:
        data = remote_directory + file
        with sftp.open(data, 'rb') as f:
            df = pd.read_csv(BytesIO(f.read()), index_col=None, header=0)
            df['FILE NAME'] = file
            content.append(df)

# Close the SFTP connection
sftp.close()

# Output frame
frame = pd.concat(content, axis=0, ignore_index=True)