Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Mu
Frequent Visitor

Attempting to Download Zipped Json File

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.

Mu_0-1688065716378.png

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

Next, I have this.

Mu_1-1688065829484.png

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.

Mu_2-1688066453073.png

 

 

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.

 

1 ACCEPTED SOLUTION
ppm1
Solution Sage
Solution Sage

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

 

ppm1_0-1688165569549.png

 

 

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

Microsoft Employee

View solution in original post

3 REPLIES 3
ppm1
Solution Sage
Solution Sage

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"

 

ppm1_0-1688085549826.png

 

Pat

Microsoft Employee
Mu
Frequent Visitor

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
Solution Sage
Solution Sage

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

 

ppm1_0-1688165569549.png

 

 

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

Microsoft Employee

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.