Web data source connection

[ojhaashok]

Hi All,

The below link is public data from fed.

https://www.ffiec.gov/npw/FinancialReport/DataDownload

Is it possible to connect to one of the csv file on this site from Power query? It should be real time when we refresh dataset to read power query data from the site.

Thank you,

AK

[lbendlin · Accepted solution]

https://www.ffiec.gov/npw/FinancialReport/ReturnAttributesActiveZipFileCSV

You would then use your favorite unzipper to extract the actual CSV.

Note:  Your ZIP files are malformed (missing the Extra Information)

UnzipNoExtra:

(ZIPFile) =>
let
    //read the entire ZIP file into memory - we'll use it often so this is worth it
    Source = Binary.Buffer(ZIPFile),
    // get the full size of the ZIP file
    Size = Binary.Length(Source),
    //Find the start of the central directory at the sixth to last byte
    Directory = BinaryFormat.Record([ 
                    MiscHeader=BinaryFormat.Binary(Size-6), 
                    Start=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian)
            ]) ,
    Start = Directory(Source)[Start],
    //find the first entry in the directory and get the compressed file size
    FirstDirectoryEntry = BinaryFormat.Record([ 
                    MiscHeader=BinaryFormat.Binary(Start+20), 
                    DataSize=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
                    UnCompressedFileSize=BinaryFormat.Binary(4),
                    FileNameLen=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian)
            ]) ,
    //figure out where the raw data starts            
    Offset = 30+FirstDirectoryEntry(Source)[FileNameLen],
    Compressed = FirstDirectoryEntry(Source)[DataSize]+1,
    //get the raw data of the compressed file
    Raw = BinaryFormat.Record([
                    Header=BinaryFormat.Binary(Offset),
                    Data=BinaryFormat.Binary(Compressed)//,
            ]) 
    // inflate it
in 
    Binary.Decompress(Raw(Source)[Data], Compression.Deflate)

finally:

let
    Source = UnzipNoExtra(Web.Contents("https://www.ffiec.gov/npw/FinancialReport/ReturnAttributesActiveZipFileCSV")),
    #"Imported CSV" = Csv.Document(Source,[Delimiter=",", Columns=74, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true])
in
    #"Promoted Headers"