Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi There,
I'm interested in importing Petroleum Data from the EIA. https://www.eia.gov/opendata/bulkfiles.php
from here I downloaded the petroleum data: http://api.eia.gov/bulk/PET.zip
The format of the file is a text file that I downloaded from this website (originally a zip file that I extracted). When I attempted to load this text file into Power BI desktop, unfortunately Power BI was unable to recognize the file. I tried various delimiters to get Power BI to recognize the file format. However, I was unable to reach an effective solution.
According to EIA's website: . The extracted text file contains all the dataset's series (ordered by most recent update data first) followed by all of the dataset's categories (ordered by hierarchy from top to bottom). Each line of the extracted text file is a complete object in JSON.
Could I please get some help one which keystrokes are required to view this data effectively in Power BI?
Thanks a lot
Peter
Solved! Go to Solution.
Thanks for your response, unfortunately I received errors when trying your method:
Specifically, the Power BI error mentioned: Unable to connect, we encountered an error while trying to connect, Details: "We found extra errors at the end of JSON input"
As a result, I searched this forum for a similar error message, and came across this tip:
Found the same issue when trying to load a JSONL file (http://jsonlines.org/). To solve it, I ended up importing it as a text file (which takes one record per line) and then using the Parse function in PowerQuery to parse the JSON in each line.
Eventually I was able to get a successful result by using the Parse Function
Hey @pwares ,
this is how the data looks like
To achieve this I did the following after extracting the txt file from the zip archive
Connect to a JSON file
I just changed the file type to All files (*.*)
I choose the PET.txt file
Voila, hopefully this is what you are looking for.
Regards,
Tom
Thanks for your response, unfortunately I received errors when trying your method:
Specifically, the Power BI error mentioned: Unable to connect, we encountered an error while trying to connect, Details: "We found extra errors at the end of JSON input"
As a result, I searched this forum for a similar error message, and came across this tip:
Found the same issue when trying to load a JSONL file (http://jsonlines.org/). To solve it, I ended up importing it as a text file (which takes one record per line) and then using the Parse function in PowerQuery to parse the JSON in each line.
Eventually I was able to get a successful result by using the Parse Function
Hi @pwares ,
Could text connectors meet your needs?
You could split columns or expand columns as you like in transform data.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |