Attempting to Download Zipped Json File

[Mu]

I am trying to downlaoad a bulk data file from the EIA (https://www.eia.gov/opendata/bulk/PET.zip). I've been going through a few forums and have gotten this far.

Copied this from https://sql10.blogspot.com/2016/06/reading-zip-files-in-powerquery-m.html

Next, I have this.

So far so good. However, when I right-click on the Content column and try to transform as JSON, I get this error.

DataFormat.Error: We found extra characters at the end of the JSON input.
Details:
Value={
Position=31950

After clicking on the last step and looking at advanced editor, this is what I get.

I understand what this means, but am confused because I have manually downloaded the same data file to my local hard drive, unzipped it, imported as JSON into power query, and it works just fine. Is there an issue with the function and being converted to binary? Is there a way to somehow skip or have a default correction for all errors?

The whole purpose of trying to get it directly from online is so that I won't have to manually download and upload the file to refresh. If there is a more efficient solution to achieve this goal, please let me know. Thanks.

[ppm1]

Looks like it is a csv file that has at least one field with nested JSON. Please try this instead.

let
    Source = Web.Contents("https://www.eia.gov/opendata/bulk/PET.zip"),
    Custom1 = UnzipContents(Source),
    Content = Custom1{0}[Content],
    #"Imported CSV" = Csv.Document(Content,null,{0, 43, 55, 78, 261},ExtraValues.Ignore,1252)
in
    #"Imported CSV"

Pat

[Mu]

Hi,

Inputting your code gave the same response that you have in the picture, but the data seems to be all messed up. If you look in the columns, the fields are all separated and I don't know of a way to use it in a chart like that.

Thanks

[ppm1 · Accepted solution]

Try this instead where each row is a complete JSON string and Json.Document is used to parse it. 

let
    Source = Web.Contents("https://www.eia.gov/opendata/bulk/PET.zip"),
    Custom1 = UnzipContents(Source),
    Content = Custom1{0}[Content],
    #"Imported CSV" = Csv.Document(Content,null,{0},ExtraValues.Ignore,1252),
    #"Added Custom" = Table.AddColumn(#"Imported CSV", "Custom", each Json.Document([Column1])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Columns", "Custom", {"series_id", "name", "units", "f", "unitsshort", "description", "copyright", "source", "iso3166", "geography", "geography2", "start", "end", "last_updated", "data"}, {"series_id", "name", "units", "f", "unitsshort", "description", "copyright", "source", "iso3166", "geography", "geography2", "start", "end", "last_updated", "data"})
in
    #"Expanded Custom"

Pat