Forum Discussion
Power BI Report creation through Script
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,
So for through the script I am able to a basic skeleton that includes the schema which is been inferred from a Meta Data sheet which looks like:
Through which I am creating the model.bim file which is kind of a json file. And even created measures by inferring the MetaData sheet (for now converted the formula's into appropriate Power BI expression manually {within the sheet}, and to keep the measures intact created a table that stores all the measures, so that later when the data source gets connected the measures aren't lost)
Now the thing is as of while creating the report.json I have explicitly specified (kind of hard-coding) the columns and the visual taht's to be used, this works but when I tried to generate the visuals (create the report.json by inferring the MetaData sheet, the structure was not right due to which even after opening the report the visuals weren't rendered properly (i.e. even after connecting the data source there was no option to add columns) which indicated there is an issue in report.json).
So any inputs on the same how can I make this dynamic as well.
Regards,
Sidhant
Hi Sidhant ,
Thanks for reaching out to the Microsoft fabric community forum.
Please follow below Steps to Dynamically Generate Valid report.json
1. Infer and map dataRoles dynamically from metadata: Every visual has expected data roles (e.g., Axis, Values, Legend).
These must be declared and bound correctly in the JSON:json code:
"dataRoles": [
{ "name": "Axis", "values": ["Orders.Product ID"] },
{ "name": "Values", "values": ["Orders.Profit"] }
]Note: You can map roles using a config dictionary per visual type (e.g., bar chart → Axis, Values) and match it with fields in your metadata.
2. Use queryRef properly: Each field reference should match the model structure:
json code:
"queryRef": {
"Product ID": "Orders.Product ID",
"Profit": "Orders.Profit"
}Note: If your model.bim defines aliases or you’ve renamed fields, this mapping must be consistent.
3. Validate visual type GUIDs / identifiers: Some of the custom visuals in your screenshot are not rendering because they aren’t included in the report package or referenced improperly. Either: Stick with default visuals Power BI supports out of the box (e.g., bar chart, line chart), or For custom visuals, ensure you: Import them into the report manually or programmatically (pbiviz.json). Reference their visualClassName and customVisualName properly in the visual payload.
4. Auto-generate visual layout placeholders: Create a generic grid or layout algorithm (e.g., page index * height/width) to avoid visuals overlapping or missing position info:
json code:
"position": {
"x": 0,
"y": 0,
"z": 0,
"width": 300,
"height": 200
}Sample example: Template for One Visual in report.json
json code:
{
"visualType": "barChart",
"dataViewMappings": [
{
"conditions": [{}],
"categorical": {
"categories": {
"for": { "in": "Product ID" }
},
"values": {
"select": [{ "bind": "Profit" }]
}
}
}
],
"dataRoles": [
{ "name": "Axis", "values": ["Product ID"] },
{ "name": "Values", "values": ["Profit"] }
],
"queryRef": {
"Product ID": "Orders.Product ID",
"Profit": "Orders.Profit"
},
"position": {
"x": 0,
"y": 0,
"z": 0,
"width": 300,
"height": 200
}
}If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and RegardsHi Sidhant ,
If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.
Thank youHi @Sidhant ,
If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.
Thank you
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
