How can I extract a list of used and unused measures and columns from Power BI project folders using Python

0 votes
How can I extract a list of used and unused measures and columns from Power BI project folders using Python?

I need to analyze a Power BI project folder to identify which measures and columns are actively used in reports and which ones are unused. This is to optimize performance by removing unnecessary fields. Are there Python libraries or scripts that can parse Power BI metadata to extract this information? What techniques or best practices can help automate this process effectively?
3 hours ago in Power BI by Evanjalin
• 15,820 points
15 views

1 answer to this question.

0 votes

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.

answered 3 hours ago by anonymous
• 15,820 points

Related Questions In Power BI

0 votes
1 answer

How can I retrieve distinct values from multiple columns using Power BI?

In Power BI, obtaining unique values from ...READ MORE

answered Oct 23, 2024 in Power BI by pooja
• 14,980 points
191 views
0 votes
1 answer

How can I retrieve a mapped value from a many-to-one related table in Power BI when using DirectQuery mode?

Get the associated values per multiple linked ...READ MORE

answered Jan 23 in Power BI by pooja
• 14,980 points
48 views
0 votes
1 answer

Displaying Table Schema using Power BI with Azure IoT Hub

Answering your first question, Event Hubs are ...READ MORE

answered Aug 1, 2018 in IoT (Internet of Things) by nirvana
• 3,130 points
1,497 views
+1 vote
1 answer

Unable to install connector for Power Bi and PostgreSQL

I think the problem is not at ...READ MORE

answered Aug 22, 2018 in Power BI by nirvana
• 3,130 points
2,855 views
+2 votes
2 answers

Migrate power bi collection to power bi embedded

I agree with Kalgi, this method is ...READ MORE

answered Oct 11, 2018 in Power BI by Hannah
• 18,520 points
1,632 views
+1 vote
1 answer

Connect power bi desktop to dataset and create custom reports

Yes using Power BI REST API to ...READ MORE

answered Sep 18, 2018 in Power BI by Kalgi
• 52,350 points
1,771 views
0 votes
1 answer

Why am I getting a 401 Unauthorized error when renaming a Power BI dataflow using the Power BI REST API, and how can I fix it?

A 401 Unauthorized error when renaming a ...READ MORE

answered 1 day ago in Power BI by anonymous
• 15,820 points
60 views
0 votes
1 answer

Why do Power BI visuals created using Python scripts sometimes appear misaligned, and how can this be fixed?

So, while writing a Python script to ...READ MORE

answered 4 days ago in Power BI by anonymous
• 15,820 points
26 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP