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
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.
Solved! Go to 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
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
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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
68 | |
64 | |
27 | |
18 | |
13 |