Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

How to unzip and decompress data extract URL in PBI Query Editor

Hi

I am looking for a bit of M code with Query editor that will unzip and decompress a zip file from a web URL export link

The data would be in the form of CSV file once exported and unzipped, its quite a large file (file over 2.5 million rows)

the web url link i am trying to extract/unzip and decompress within PBI is 

https://www.doogal.co.uk/files/postcodes.zip

 

can anyone help with the M query code i would need for this?

 

Many thanks

Dan

Need Help

1 Reply

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago

    You can use any of the examples (I added one of my older blog entries at the bottom.

     

    Here is your main query

    let
    Source = Unzip(Web.Contents("https://www.doogal.co.uk/files/postcodes.zip")),
    #"Imported CSV" = Csv.Document(Source,[Delimiter=",", Columns=53, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true])
in
    #"Promoted Headers"

    and here is the (slightly modified) Unzip function:

    (ZIPFile as binary) as binary =>
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), 
                    FileSize=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
                    UnCompressedFileSize=BinaryFormat.Binary(4),
                    FileNameLen=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),
                    ExtrasLen=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian)
            ]) ,
    //figure out where the raw data starts            
    Offset = 30+FirstDirectoryEntry(Source)[FileNameLen]+FirstDirectoryEntry(Source)[ExtrasLen],
    Compressed = FirstDirectoryEntry(Source)[FileSize]+1,
    //get the raw data of the compressed file
    Raw = BinaryFormat.Record([
                    Header=BinaryFormat.Binary(Offset), 
                    Data=BinaryFormat.Binary(Compressed)
            ]) 
    // unzip it
in 
    Binary.Decompress(Raw(Source)[Data], Compression.Deflate)

     

    (1) Working With Zip Files in Power Query - Microsoft Power BI Community