Register
Log In
Unanswered
Home
April 24, 2014
Offline
I am currently experimenting with loading a JSON file containing lots of tables with records into Excel with Power Query, and into Power BI as well. However in PQ it seems like you can get data from web, but not directly from a JSON file. Can this be true? Has anybody been experimenting with that? The purpose is to load all of the content into several tables, if possible.
November 8, 2013
Offline
Of course it's possible.
The JSON string must probably is saved in a text file, with .txt extension.
You can use the option From File-From Text, and when the power query window is open, go to the Source step settings and open file as Json.
See the attached image.
Cheers,
Catalin
April 24, 2014
Offline
Thanks a lot, I had not thought of that opportunity, although it seems quite obvious!
Now that I've loaded the file, I see a table with lots of records (shown in picture if loaded properly), and each of these records contains more records or lists.
Do you know the best way to unfold these records into tables, that I afterwards can combine in the data model? Is "Add as new query" the only possibility, and if yes, can this be written in "M" language so that file load into Power Query doesn't require multiple clicks?
Or, would it be better to better specify a different output of the JSON-file? Maybe in a more "tabular format"?
Thanks,
Marianne
November 8, 2013
Offline
Hi Marianne,
JSON strings can be anywhere from simple to very complex, on multiple levels, some fields can hold simple values, other fields can have arrays of values.
You should write a query for each table, you might not be able to combine them in a single query. If you can upload a sample JSON text file, with few sample records, I can take a look at the structure, to see what can be done.
Cheers,
Catalin
April 24, 2014
Offline
Hi again,
Here is the file in question. The big question is perhaps whether the file itself should be structured differently in order to more easily retrieve data with Power Query.
Any input is much appreciated!
Thanks,
Marianne
November 8, 2013
Offline
Hi Marianne,
The json string is not formatted properly, Power query and other tools that can process this type of data will not be able to process it.
Take a look at Online jsonformatter
You can paste there the json text you have, and validate it. There are 3 standards for json formatting, your string cannot be validated against any standard.
Check the source of that file, if that cannot produce a valid json string, you will not be able to process it easily.
Catalin
Most Users Ever Online: 57
Currently Online: Mynda Treacy
18 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
sugi gunawan
janja mesec
John DiMartino
lizette acosta
eric johnson
Kim Bond
Nichelle Stevens
Manuel Rodriguez
peter wang
Amit Kanekar
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
