Forum Discussion
Solved
Power BI Report creation through Script
Hi everyone, So recently I was experimenting that can we create reports through script apart from Power BI desktop and I found out that we can do it by using the '.pbip' file extension. And I did fo...
Hi everyone,
A quick update so I am able to create reports in a programatic fashion it looks like:def create_model_bim(tables, output_folder): # Load a fresh copy of the model template model_bim = copy.deepcopy(model_template) # Replace {{query_order}} annotation for annotation in model_bim["model"].get("annotations", []): if annotation.get("value") == "{{query_order}}": annotation["value"] = str([tbl["name"] for tbl in tables]) # Build table_column_map to validate relationships later table_column_map = {} with open("Static/data_type_mapping.json") as f: type_mapping = json.load(f) model_bim["model"]["tables"] = [] for table in tables: column_names = [col["name"] for col in table["columns"]] table_dict = { "name": table["name"], "columns": [ { "name": col["name"], "dataType": type_mapping.get(col["type"].lower(), "string"), "sourceColumn": col["name"] } for col in table["columns"] ], "partitions": [ { "name": f"{table['name']}_Partition", "mode": "import", "source": { "type": "m", "expression": ( f"let Source = Sql.Database(\"server_name\", \"database_name\") " f"in Source{{[Schema=\"dbo\", Item=\"{table['name']}\"]}}[Data]" ) } } ] } model_bim["model"]["tables"].append(table_dict) # table_column_map[table["name"]] = [col["name"] for col in table["columns"]] table_column_map[table["name"]] = column_names # ✅ Load relationships sheet and parse valid entries # metadata_path = os.path.join(os.path.dirname(output_folder), "Files", "MetaData.xlsx") metadata_path = os.path.abspath(os.path.join(output_folder, "..", "..", "Files", "MetaData.xlsx")) # relationships = extract_relationships_from_metadata(metadata_path) relationships = extract_relationships_from_metadata(metadata_path, table_column_map) if relationships: model_bim["model"]["relationships"] = relationships measures = extract_measures_from_metadata(metadata_path) if measures: measures_table = { "name": "__Measures", "columns": [ { "name": "Dummy", "dataType": "string" } ], "measures": measures, "partitions": [ { "name": "__Measures_Partition", "mode": "import", "source": { "type": "m", "expression": "let Source = #table({\"Dummy\"}, {}) in Source" } } ] } model_bim["model"]["tables"].append(measures_table) # Write model.bim to file bim_path = os.path.join(output_folder, "model.bim") with open(bim_path, "w", encoding="utf-8") as file: json.dump(model_bim, file, indent=4) print(f"✅ model.bim created at: {bim_path}") #-- Report.json file creation: def create_valid_report_json(report_folder_path, chart_types_df, chart_axes_df): base_config = copy.deepcopy(report_static_config["config"]) def create_visual_config(visual_type, visual_index, axes): visual_id = str(uuid.uuid4()) layout = { "id": 0, "position": { "x": 100.0 + (visual_index % 2) * 450.0, "y": 100.0 + (visual_index // 2) * 350.0, "z": 0, "width": 400.0, "height": 300.0, "tabOrder": 0 } } projections = {} selects = [] from_tables = set() for axis_type, axis_list in axes.items(): projections[axis_type] = [] for axis in axis_list: table = axis['table_name'] column = axis['column'] full_ref = f"{table}.{column}" from_tables.add(table) if axis.get('aggregation'): agg_func = 0 # sum projections[axis_type].append({"queryRef": f"Sum({full_ref})"}) selects.append({ "Aggregation": { "Expression": { "Column": { "Expression": {"SourceRef": {"Source": table}}, "Property": column } }, "Function": agg_func }, "Name": f"Sum({full_ref})", "NativeReferenceName": f"Sum of {column}" }) else: projections[axis_type].append({"queryRef": full_ref, "active": True}) selects.append({ "Column": { "Expression": {"SourceRef": {"Source": table}}, "Property": column }, "Name": full_ref, "NativeReferenceName": column }) visual_config = { "name": visual_id, "layouts": [layout], "singleVisual": { "visualType": visual_type, "projections": projections, "prototypeQuery": { "Version": 2, "From": [{"Name": t, "Entity": t, "Type": 0} for t in from_tables], "Select": selects }, "drillFilterOtherVisuals": True, "hasDefaultSort": True, "objects": {}, "vcObjects": { "title": [ { "properties": { "text": { "expr": { "Literal": { "Value": f"'{visual_type.title()} Visual {visual_index + 1}'" } } } } } ] } } } return visual_config visual_containers = [] for idx, row in chart_types_df.iterrows(): visual_type = row.get('plot_type') worksheet = row.get('worksheet') if not visual_type or not worksheet: continue # Skip incomplete rows relevant_axes = chart_axes_df[ (chart_axes_df['worksheet'] == worksheet) & (chart_axes_df['type'].isin(['rows', 'cols'])) & # (chart_axes_df['order_id'] == 0) & (chart_axes_df['table_name'].notna()) ] axes_dict = {} for axis_type in ['rows', 'cols']: group = relevant_axes[relevant_axes['type'] == axis_type] if not group.empty: axes_dict['Category' if axis_type == 'rows' else 'Y'] = group.apply( lambda axis_row: { 'table_name': axis_row['table_name'], 'column': axis_row['column'], 'aggregation': str(axis_row.get('aggregation', '')).strip().lower() == 'sum' }, axis=1 ).tolist() if axes_dict: config = create_visual_config(visual_type, idx, axes_dict) container = { "config": json.dumps(config), "filters": "[]", "height": 300.0, "width": 400.0, "x": 100.0 + (idx % 2) * 450.0, "y": 100.0 + (idx // 2) * 350.0, "z": 0.0 } visual_containers.append(container) report_json = { "config": json.dumps(base_config), "layoutOptimization": 0, "resourcePackages": [], "sections": [ { "config": "{}", "displayName": "Auto Page", "displayOption": 1, "filters": "[]", "height": 720.0, "name": str(uuid.uuid4()), "visualContainers": visual_containers, "width": 1280.0 } ] } output_path = os.path.join(report_folder_path, "report.json") with open(output_path, "w", encoding="utf-8") as f: json.dump(report_json, f, indent=4) print(f"✅ report.json with {len(visual_containers)} visuals written at: {output_path}")
Hi v-dineshya ,
Thanks for the reply, I will check this out. As of now when I am creating the report.json (within my Python script I have hard coded the structure what columns need to be picked)
def create_valid_report_json(report_folder_path):
section_id = str(uuid.uuid4())
section_id_page2 = str(uuid.uuid4())
visual_id_chart = str(uuid.uuid4())
visual_id_table = str(uuid.uuid4())
visual_id_pie = str(uuid.uuid4())
visual_id_gauge = str(uuid.uuid4())
# Copy base config
base_config = copy.deepcopy(report_static_config["config"])
# === 1. Bar Chart Visual ===
bar_chart_config = {
"name": visual_id_chart,
"layouts": [
{
"id": 0,
"position": {
"x": 100.0,
"y": 100.0,
"z": 0,
"width": 400.0,
"height": 300.0,
"tabOrder": 0
}
}
],
"singleVisual": {
"visualType": "columnChart",
"projections": {
"Y": [{"queryRef": "Sum(Orders.Sales)"}],
"Category": [{"queryRef": "Orders.Customer Name", "active": True}]
},
"prototypeQuery": {
"Version": 2,
"From": [
{"Name": "Orders", "Entity": "Orders", "Type": 0}
],
"Select": [
{
"Aggregation": {
"Expression": {
"Column": {
"Expression": {"SourceRef": {"Source": "Orders"}},
"Property": "Sales"
}
},
"Function": 0
},
"Name": "Sum(Orders.Sales)",
"NativeReferenceName": "Sum of Sales"
},
{
"Column": {
"Expression": {"SourceRef": {"Source": "Orders"}},
"Property": "Customer Name"
},
"Name": "Orders.Customer Name",
"NativeReferenceName": "Customer Name"
}
],
"OrderBy": [
{
"Direction": 2,
"Expression": {
"Aggregation": {
"Expression": {
"Column": {
"Expression": {"SourceRef": {"Source": "Orders"}},
"Property": "Sales"
}
},
"Function": 0
}
}
}
]
},
"drillFilterOtherVisuals": True,
"hasDefaultSort": True,
"objects": {},
"vcObjects": {
"title": [
{
"properties": {
"text": {
"expr": {
"Literal": {"Value": "'Sales by Customer'"}
}
}
}
}
]
}
}
}
# === 2. Table Visual ===
table_visual_config = {
"name": visual_id_table,
"layouts": [
{
"id": 0,
"position": {
"x": 550.0,
"y": 100.0,
"z": 0,
"width": 500.0,
"height": 300.0,
"tabOrder": 0
}
}
],
"singleVisual": {
"visualType": "tableEx",
"projections": {
"Values": [
{"queryRef": "Orders.Customer Name"},
{"queryRef": "Sum(Orders.Sales)"}
]
},
"prototypeQuery": {
"Version": 2,
"From": [
{"Name": "Orders", "Entity": "Orders", "Type": 0}
],
"Select": [
{
"Column": {
"Expression": {"SourceRef": {"Source": "Orders"}},
"Property": "Customer Name"
},
"Name": "Orders.Customer Name",
"NativeReferenceName": "Customer Name"
},
{
"Aggregation": {
"Expression": {
"Column": {
"Expression": {"SourceRef": {"Source": "Orders"}},
"Property": "Sales"
}
},
"Function": 0
},
"Name": "Sum(Orders.Sales)",
"NativeReferenceName": "Sum of Sales"
}
]
},
"drillFilterOtherVisuals": True,
"objects": {},
"vcObjects": {
"title": [
{
"properties": {
"text": {
"expr": {
"Literal": {"Value": "'Customer Sales Table'"}
}
}
}
}
]
}
}
}
# Page-2
# Pie chart
pie_chart_config = {
"name": visual_id_pie,
"layouts": [{
"id": 0,
"position": {
"x": 100.0, "y": 100.0, "z": 0,
"width": 400.0, "height": 300.0,
"tabOrder": 0
}
}],
"singleVisual": {
"visualType": "donutChart",
"projections": {
"Category": [{"queryRef": "Orders.Segment"}],
"Values": [{"queryRef": "Sum(Orders.Sales)"}]
},
"prototypeQuery": {
"Version": 2,
"From": [{"Name": "Orders", "Entity": "Orders", "Type": 0}],
"Select": [
{
"Column": {
"Expression": {"SourceRef": {"Source": "Orders"}},
"Property": "Segment"
},
"Name": "Orders.Segment"
},
{
"Aggregation": {
"Expression": {
"Column": {
"Expression": {"SourceRef": {"Source": "Orders"}},
"Property": "Sales"
}
},
"Function": 0
},
"Name": "Sum(Orders.Sales)"
}
]
},
"objects": {},
"vcObjects": {
"title": [{
"properties": {
"text": {
"expr": {
"Literal": {"Value": "'Sales by Segment'"}
}
}
}
}]
}
}
}
# -- Gauge --
gauge_chart_config = {
"name": visual_id_gauge,
"layouts": [{
"id": 0,
"position": {
"x": 550.0, "y": 100.0, "z": 0,
"width": 400.0, "height": 300.0,
"tabOrder": 0
}
}],
"singleVisual": {
"visualType": "gauge",
"projections": {
"Y": [{"queryRef": "Sum(Orders.Profit)"}]
},
"prototypeQuery": {
"Version": 2,
"From": [{"Name": "Orders", "Entity": "Orders", "Type": 0}],
"Select": [
{
"Aggregation": {
"Expression": {
"Column": {
"Expression": {"SourceRef": {"Source": "Orders"}},
"Property": "Profit"
}
},
"Function": 0
},
"Name": "Sum(Orders.Profit)"
}
]
},
"objects": {},
"vcObjects": {
"title": [{
"properties": {
"text": {
"expr": {
"Literal": {"Value": "'Profit Gauge'"}
}
}
}
}]
}
}
}
# === Final Report JSON ===
report_json = {
"config": json.dumps(base_config),
"layoutOptimization": report_static_config.get("layoutOptimization", 0),
"resourcePackages": report_static_config.get("resourcePackages", []),
"sections": [
{
"config": "{}",
"displayName": "Page 1",
"displayOption": 1,
"filters": "[]",
"height": 720.0,
"name": section_id,
"visualContainers": [
{
"config": json.dumps(bar_chart_config),
"filters": "[]",
"height": 300.0,
"width": 400.0,
"x": 100.0,
"y": 100.0,
"z": 0.0
},
{
"config": json.dumps(table_visual_config),
"filters": "[]",
"height": 300.0,
"width": 500.0,
"x": 550.0,
"y": 100.0,
"z": 0.0
}
],
"width": 1280.0
},
{
"config": "{}",
"displayName": "Page 2",
"displayOption": 1,
"filters": "[]",
"height": 720.0,
"name": section_id_page2,
"visualContainers": [
{
"config": json.dumps(pie_chart_config),
"filters": "[]",
"height": 300.0,
"width": 400.0,
"x": 100.0,
"y": 100.0,
"z": 0.0
},
{
"config": json.dumps(gauge_chart_config),
"filters": "[]",
"height": 300.0,
"width": 400.0,
"x": 550.0,
"y": 100.0,
"z": 0.0
}
],
"width": 1280.0
}
]
}
output_path = os.path.join(report_folder_path, "report.json")
with open(output_path, "w", encoding="utf-8") as f:
json.dump(report_json, f, indent=4)
print(f"✅ report.json with bar chart + table written at: {output_path}")
Instead I was thinking to templatize this, wherein as per requirement I will just fill the table name and the columns and it will fill them in the respective positions
//report.json
"visualContainers": [
{
"config": "{\"name\": \"d8ac8514-74a7-4abb-b788-d338689a4975\", \"layouts\": [{\"id\": 0, \"position\": {\"x\": 100.0, \"y\": 100.0, \"z\": 0, \"width\": 400.0, \"height\": 300.0, \"tabOrder\": 0}}], \"singleVisual\": {\"visualType\": \"columnChart\", \"projections\": {\"Y\": [{\"queryRef\": \"Sum(Orders.Sales)\"}], \"Category\": [{\"queryRef\": \"Orders.Customer Name\", \"active\": true}]}, \"prototypeQuery\": {\"Version\": 2, \"From\": [{\"Name\": \"Orders\", \"Entity\": \"Orders\", \"Type\": 0}], \"Select\": [{\"Aggregation\": {\"Expression\": {\"Column\": {\"Expression\": {\"SourceRef\": {\"Source\": \"Orders\"}}, \"Property\": \"Sales\"}}, \"Function\": 0}, \"Name\": \"Sum(Orders.Sales)\", \"NativeReferenceName\": \"Sum of Sales\"}, {\"Column\": {\"Expression\": {\"SourceRef\": {\"Source\": \"Orders\"}}, \"Property\": \"Customer Name\"}, \"Name\": \"Orders.Customer Name\", \"NativeReferenceName\": \"Customer Name\"}], \"OrderBy\": [{\"Direction\": 2, \"Expression\": {\"Aggregation\": {\"Expression\": {\"Column\": {\"Expression\": {\"SourceRef\": {\"Source\": \"Orders\"}}, \"Property\": \"Sales\"}}, \"Function\": 0}}}]}, \"drillFilterOtherVisuals\": true, \"hasDefaultSort\": true, \"objects\": {}, \"vcObjects\": {\"title\": [{\"properties\": {\"text\": {\"expr\": {\"Literal\": {\"Value\": \"'Sales by Customer'\"}}}}}]}}}",
"filters": "[]",
"height": 300.0,
"width": 400.0,
"x": 100.0,
"y": 100.0,
"z": 0.0
}So in the above snippet I wanted to templatize this (was thinking to store a template and wherever needed like : Source: table (here it is Orders); queryRef: Column_name (Table_Name.Column_Name; Orders.CustomerName)
So as of now I already have created a Static folder wherein I have kept some bolier code, like for report.json, model.bim; in similar fashion can we templatize this such that when I run the script I replace the placeholders (in config) and don't need to explicitly mention the visuals and the columns.
And the other thing is if I need to have a filter (page level on a columns what should be the structure for that; i.e. how to add that?)
Apologise for the late response
Regards,
Sidhant.
Hi Sidhant ,
Thank you for reaching out to the Microsoft Community Forum.
I can't replicate the issue. It's advisable to raise a support ticket with Microsoft for further backend investigation.
How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn
If my response solved your query, please mark it as the "Accepted solution" to help others find it easily.
And if my answer was helpful, I'd really appreciate a "Kudos".
Thanks
Hi v-dineshya ,
Ok, thanks for the clarification I will have a look. Just to give you an update now I am able to resolve the issue able to create visuals and the schema (tables and the respective columns) from the Metadata file.
Regards,
SidhantHi Sidhant ,
We are pleased to hear that you have found a workaround. Please share the details here and mark it as "Accept as solution" to assist others with similar issues.
Thank you.