Reading .gz file produced by running a URL

[Alirezam]

Hi Experts,

I have a URL that every day has to be run automatically to produce a GZ file (containing a JSON file). I need to restore this GZ file in a folder and read this folder by Power Bi every day. Do you have any idea how I can restore these GZ files and how to ask Power Bi to read, extract, and transform the data? Do I need to use 'web' or 'Jason' or 'folder' as the source of data?

Any help will be appreciated.

Thanks

This is the command:

https://airbox.edimaxcloud.com/api/tk/query_day?token=823d52ab-86a2-48ba-b092-dc08f2987757

[lbendlin]

Edit 3:  

it's actually the deflation used by your host that causes the issue. I extracted the JSON via 7Zip , added it back into a fresh gz archive and then could successfully read it with Power Query.

Maybe you can do something similar - use a separate decompression tool? 

=========================

Edit 2:

confirming this works for regular gz files. Your file comes with an "Unknown OS" flag, without file name header, and with a couple other quirks that are just too much for Power Query. You can either ask the data source owner to improve the format, or you can extract the raw data as shown below and then add a "nicer"  header and footer to it before decompressing.

==============

Edit: Supposedly Power Query supports GZip natively, like so:

Data = Binary.Decompress(File.Contents("c:\users\xxx\Downloads\query_day.gz"), Compression.GZip)

however I still cannot see more than the first 34 bytes of the decompressed file. Not sure if the Deflate algorithm is to blame, or something else is going on.  Will test with a different file

=============

Search the blog for articles about how to handle ZIP files in Power Query.

Working With Zip Files in Power Query - Microsoft Power BI Community

Hopefully the approach taken there also works for the GZ format. Its specification is here:

GZ - Gnu Zipped Archive File Format

It might be as easy as stripping away the first 10 bytes and the last 8 bytes, and then using Deflate.

let
    Source = Binary.Buffer(File.Contents("c:\users\xxx\Downloads\query_day.gz")),
    Size = Binary.Length(Source),
    Structure = BinaryFormat.Record([ Header=BinaryFormat.Binary(10), 
                    CompressedData=BinaryFormat.Binary(Size-18), 
                    Footer=BinaryFormat.Binary(8) ]) ,
    Data = Binary.Decompress(Structure(Source)[CompressedData], Compression.Deflate),
    JSON = Json.Document(Data)
in
    JSON

Note that your sample file is not well formed JSON and fails to parse.

[Alirezam]

Thanks, bro for your help. I also have trouble restoring the files before reading them as this command needs to be run every single day to generate one gz file. I appreciate your time though.

[v-jingzhang]

Hi @Alirezam 

You can use Power Automate to create a flow to trigger this URL, rename and save downloaded files into a folder every day. Opening an URL using Power Automate - Power Platform Community 

You can also use some programming methods to do this. 

Regards,
Community Support Team _ Jing