Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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"
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"
Check out the July 2025 Power BI update to learn about new features.