Import Multiple JSON files into Excel Power Query Editor

[Bingsmate]

Hi,

Has anyone managed to import multiple json files into Excel successfully? When I try to combine the data from multiple files it gives me the error:

An error occurred in the ‘Transform File’ query. Expression.Error: We cannot convert a value of type List to type Record.
Details:
Value=[List]
Type=[Type]

I am able to to expand the embedded lists etc from a single file successfully and load the base data into a table but am unable to work out how to combine and transform multiple files at once due to the lists contained in each file structure.

Has anyone been able to do this successfully and can help me out? I have found many websites that show steps on modifying the Advanced Editor code but I am yet to find a solution that works for me.

Thanks,

Danielle

[JonFitbit]

Hi, this forum is for development questions relating to Fitbit OS smartwatches.

You would probably find more help on an Excel related forum. That being said, if it was me, I'd probably focus on converting the source data JSON into something that can easily be imported, such as CSV. There are plenty of converters out there.

[Stoshman]

I've had some success using Power BI (another Microsoft product, free on desktop) - it basically is the Excel power query made into a standalone produce.  Very nice.  I can download the Fitbit account archive onto the computer and then use Power BI to pull in the single csv files (e.g. sleep score) or combine multiple csv or json files into a single data table.  It took some finagaling to get the json files to work (I'm self-taught in Power BI) but was successful.  The issue I have now is that the datetime fields are oddly formatted and some json files (maybe all, still looking) store info in GMT instead of local time.  So plotting HR vs O2 vs Sleep stage isn't necessarily aligned, so can't really process yet.  I don't anticipate this being a real problem, just a revision of the upload queries to fix the datetime to local when I can figure out which are which.

I also like excel - one option is to do the json aggregation in power bi - not sure if you can then export the query back to Excel (you very well may be able to do so), but you can definitely export the resulting data file back into an excel or csv file and open in Excel easily.

[Stoshman]

Not sure if you've solved this yet or not, but I've found Power BI (a free microsoft package) handles the json files well - you can select whatever folder they are in, set up the input and you end up with data that you can graph etc. within the tool.  The simplest graphic is a table, where you can reproduce all of the data, which can be exported to excel, if you prefer working in there.

I download my entire data file from Fitbit every few days - save them into the same location.  Then just open Power BI and refresh and get all of the various pieces (heart rate, fitbit zones, O2, HRV, temperature (though I don't do anything with it),....).  I have set up exports to create a big "1 value per day" table, which exports everything (e.g. RHR, Max HR, Min HR, Mean HR, Median HR.... - one value per day) and I save this as excel.  Once everything is read into Power BI, it is large and a little slow  (e.g. HR is every few seconds every day...) - the 1 per day serves much of the reporting I want to run, and the exports read into Excel (or, I suppose a second Power BI) are much faster.