The connector fully integrates SFTP connectivity into the two data connectivity modes in Power BI: DirectQuery and data import. Select a data connectivity mode when you create the connection to SFTP in the Get Data window.
Use DirectQuery mode to work with the remote data in real time, rather than a local copy. As you define filters, aggregate fields, or join tables, the connector executes the underlying queries to SFTP.
Note: DirectQuery mode is limited by the DirectQueryLimit connection property.
Using Data Import
Use data import mode to save a copy of the data in your report. As you make changes to your report, Power BI executes the underlying queries to the local cache, independent of the connector.
To synchronize your report with any changes in the remote data, click Refresh from the Home menu on the ribbon.
Advanced Connection Properties (optional)
This field allows you to specify properties for the connection. For example, PropertyA=Value1;PropertyB=Value2;
Advanced Options (optional)
This field allows you to provide a SQL statement that specifies what data to return. To configure this option, expand the Advanced Options area and then, in the SQL statement field, type or paste the SQL statement. Note that SQL statements are not supported in DirectQuery mode.
You can use the following types of SQL statements: