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.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
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
1 Guest(s)