The extracted measures and columns, which represent both the consumed and unused measures and columns, can be extracted from a Power BI project folder by analyzing the metadata that exists in the .pbit or .pbix file using Python.
Best Approach: Use Python to Parse Power BI Metadata
1. Use pbi-tools (Best recommended for pass-through to Extract Metadata)
PBI-tools export Power BI metadata into JSON files.
Run:
pbi-tools extract "path/to/your.pbix"
This generates a JSON model containing tables, columns, and measures.
2. Use Python to Analyze Metadata
- Extract tables, columns, and measures from the JSON files.
- Compare them against visuals in report pages.
- Example script using json:
import json
with open("Model.bim", "r") as file:
model = json.load(file)
# Extract all columns and measures
all_columns = {table["name"]: [col["name"] for col in table.get("columns", [])] for table in model["tables"]}
all_measures = {table["name"]: [measure["name"] for measure in table.get("measures", [])] for table in model["tables"]}
print("Columns:", all_columns)
print("Measures:", all_measures)
3. Identify Used vs. Unused Elements
- Parse report visuals (Report\Layout.json) and check which columns/measures are referenced.
- Use set operations in Python to find unused fields.
Automation Tip:
- Combine with Power BI REST API to extract datasets dynamically.
- Use pandas for further analysis and filtering.