Our team manager has decided that we should use Trello to manage our work on a project. There does not seem to be any out of the box MI reporting in Trello, not the free version at least, so he asked me to look at if we could report using Excel ‘or something’. You can export your board as a JSON file on the free version (if you get the business package you can export to CSV as well). Since right now my only knowldge of JSON is how to spell and pronounce it. I tried just sucking it into PowerBI but that only produced one row. Same happened with Excel so I guess it’s a PowerQuery ‘feature’.
I found some sites that will convert JSON to CSV or XLSX but most of those didn’t suit. JSON it turns out is hierarchical so that one row PowerQuery was producing was just the ‘left most’ entry and it was ignoring all further ‘child’ elements. The JSON to CSV converters did return all the elements b ut most retained the hierarchy so only the first row had all of the columns populated so PowerQuery only recognised that row.
I did find one site, Konklone, that will populate all columns so went with that.
Trello exports it’s dates in ISO 8601 format which Excel doesn’t know what to do with. After some digging and experimentation I found that the best way was to create a calculated column using the datavalue() and left() and mid() functions like:
=DATE(LEFT([@Cdate], 4), MID([@Cdate],6,2), MID([@Cdate],9,2))
to keep the time portion you can use:
=DATEVALUE(MID([@Cdate], 1, 10))+TIMEVALUE(MID([@Cdate], 12, 8))
This, of course, ignores the timezone element and gives the time in Coordinated Universal Time (UTC) but since UTC is synonymous with Greenwich Mean Time, we are in the UK and are unlikley to be that bothered about the time element that’s not a problem for us. Worth remembering for future reference though.
Stephen is learning data analysis 