Need To Create Report From CSV File using it's APIs

[UmeshGoti]

Hello Community,

I have requirement to generate powerbi report including visual part or semantic layer by having CSV as datasource through poewrBi API's .
Generated report will fetch data from CSV and CSV can be stored in some storage space like Amazon S3 Bucket or Azure Storage through some APIs of PowerBi.

As currently i am reading out documentations i think we can use Gateways of PowerBi(https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-deployment-guidance)
and may be semantic layer creation api(https://learn.microsoft.com/en-us/rest/api/fabric/semanticmodel/items/create-semantic-model?tabs=HTT...) to create semantic model 

Can some one please help on this. 

Thanks !!

[Anonymous · Accepted solution]

Hi, @UmeshGoti 

Here's a step-by-step solution that combines the Power BI REST API with cloud storage (S3/Azure Blob) to fully automate the generation of reports with semantic models and visualizations.

Cloud Storage Configuration:
Azure Blob example:

from azure.storage.blob import BlobServiceClient

connection_string = "your_azure_connection_string"
blob_service_client = BlobServiceClient.from_connection_string(connection_string)
container_client = blob_service_client.get_container_client("reports")

blob_client = container_client.upload_blob("data.csv", open("local_data.csv", "rb"))
sas_token = blob_client.generate_sas(permissions="r", expiry=datetime.utcnow() + timedelta(hours=2))
csv_url = f"{blob_client.url}?{sas_token}"

Use the Power BI REST API to create datasets and define table structures.
To create a dataset :

POST https://api.powerbi.com/v1.0/myorg/datasets
Authorization: Bearer [Access Token]
Content-Type: application/json

{
  "name": "SalesDataset",
  "tables": [
    {
      "name": "Sales",
      "columns": [
        {"name": "Date", "dataType": "datetime"},
        {"name": "Product", "dataType": "string"},
        {"name": "Revenue", "dataType": "Int64"}
      ]
    }
  ]
}

Load CSV data into the dataset:

POST https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}/tables/Sales/rows
Authorization: Bearer [Access Token]
Content-Type: application/json

{
  "rows": [
    {"Date": "2024-01-01", "Product": "A", "Revenue": 1000},
    {"Date": "2024-01-02", "Product": "B", "Revenue": 1500}
  ]
}

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.