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.
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
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
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
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
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
