How to load file to Excel Power query from SFTP si

Question

Can anyone please help me on this? I am trying to load a file to Excel Power Query from the SFTP site and I don't see any option. Following are the sample WINSCP SFTP site credentials:

File Protocol: SFTP
Host Name: ftp.test.com
Port Number: 22
Username: Test1
Password: XXXX

As shown in the below code snippet, I can load it from my local C drive, but Can you let me know how I can load it from SFTP drive site?

let
Source = Csv.Document(File.Contents("C:\Users\Documents\Winscp\acct_dept.dat"),[Delimiter="|", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}})
in
    #"Changed Type"

Answer 1

Currently, I don't think there is a connector that will do this.

There is an idea on the Power BI forums that you can vote for related to this.

There might be possible workarounds if you're willing to do some scripting as suggested in this Reddit thread, but I don't fully understand how that would work.