Converts PowerBI JSON payload to CSV

powerbi_json_to_csv.rb

require 'json'
require 'csv'

hash = JSON.parse(File.read('querydata.json'))

CSV.open('data.csv', 'w') do |csv|
  hash['results'].each do |result|
    data = result['result']['data']
    descriptor = data['descriptor']
    dsr = data['dsr']
    version = dsr['Version']
    raise "unrecognised version #{version.inspect}" unless version == 2

    dsr['DS'].each do |dataset|
      # dataset["N"]
      value_dicts = dataset['ValueDicts']

      dataset['PH'].each do |ph|
        structure = []
        ph['DM0'].each do |dm|
          if dm.key?('S')
            structure = dm['S']
            # puts structure.inspect
            csv << structure.map do |s|
              label_key = s['N']
              group_key = descriptor['Select'].select { |a| a['Value'] == label_key }
                                              .first['GroupKeys'].first
              group_key['Source']['Property']
            end
          end
          next if dm['C'].nil?
          # puts dm.inspect
          row = dm['C'].map.with_index do |column, i|
            dictionary_key = structure[i]['DN']
            value_dict = value_dicts[dictionary_key]
            # puts [column, i, dictionary_key, value_dict.length].inspect
            column.is_a?(String) ? column : value_dict[column]
          end
          # puts row.inspect
          csv << row
          # dm['R']
        end
      end
      # dataset["IC"]
    end
  end
end

related: https://github.com/derhuerst/fetch-bvg-occupancy/blob/1ebb864b1ff7130f9d2f0ab031c6d78bcabdd633/lib/parse-dataset.js

Is there a name for this schema? Searching for things like "Power BI JSON payload" just leads to countless threads about ingesting JSON with Power BI, not consuming this output.

https://stackoverflow.com/questions/56767004/need-documentation-for-analysis-windows-net-public-reports-querydata

@alpha-beta-soup not that I'm aware of, but let me know if you find a schema name.