How to extract fields from 'Layout' JSON file of Power BI tool with Python?

Question

I request a code modification that extracts columns from a (nested) JSON file inside a '.PBIX' file (Power BI tool) in Python. The details are below:

• Original code to extract some columns written by Mr Umberto Grando:

• Code on GitHub: https://github.com/Inzaniak/pybistuff/tree/master/pbixExtractor

• Explanation of the code on Medium: https://python.plainenglish.io/extracting-measures-and-fields-from-a-power-bi-report-in-python-1b928d9fb128

• I extracted the Layout file for your convenience in Google Drive: https://drive.google.com/drive/folders/1Z5cqgE-iuS0__G5hCl7Ge-MZW9WKmJu7?usp=sharing

Request:

• I need to extract the 'visualType' field as well in a similar format in the GitHub code above for Business Documentation. To be able to extract parameters like this in a table automatically will save hours of time in documentation.

I tried:

• JS Beautify JSON dumps, JSON LOADS, JSON Normalize, Different Encoding types, Adding ['visualTypes'] in code but didn't know how to append. I have trouble understanding the structure of the JSON file here, too.

Other tries :

!cd "path/Layout"
import json

# Opening JSON file
f = open('path/Layout', encoding='utf-16 le')

# returns JSON object as
# a dictionary
data = json.load(f)

# Iterating through the json
# list
for i in data:
    print(i)

###Output: ###

• id sections pods config reportId resourcePackages layoutOptimization publicCustomVisuals

Answer 1

Main reason this was downwoted what that you did not post any code or errors when you tried to solve this.

When I tried I got this error: json.decoder.JSONDecodeError: Expecting property name enclosed in double quotes: line 1 column 2 (char 1)

and: UnicodeDecodeError: 'utf-8' codec can't decode byte 0xa9 in position 9380: invalid start byte

This you can most likely find lots of answers to with google:

When you see byte errors in json.load() it is most likely encoding, so chardet will help you:

#!/usr/bin/env python3
import json
import chardet
raw = "C:\\Users\\mobj\\Downloads\\Layout"

with open(raw, 'rb') as FI:
    print(chardet.detect(FI.read()))

dat = {}
# encoding from chardet:
with open(raw, 'r', encoding='utf-16le', errors='ignore') as FI:
    dat = json.load(FI)

print(json.dumps(dat, indent=2, sort_keys=True))