Updating Data with Push Push Data Sets

[Anonymous]

Hi Everyone 

I am a complete NOOB at this, thrown in the deep end on a project. I have followed the tutorial (https://docs.microsoft.com/en-us/power-bi/developer/walkthrough-push-data), and the solution works fine. I tried adding a column to the table (NumberSold) and set the initial value to 1, now I need to update that value to say 5. I cannot find a solution from the API reference, and all my attempts end up with new rows. Searched the forum and cannot see a solution. 

I surmise that I will have to delete all the rows and refresh, which could be a problem, given the ultimate size of the dataset, I am being asked to create. 

The code is 

private static void AddRows(string datasetId, string tableName)
{
string powerBIApiAddRowsUrl = String.Format("https://api.powerbi.com/v1.0/myorg/datasets/{0}/tables/{1}/rows", datasetId, tableName);

HttpWebRequest request = System.Net.WebRequest.Create(powerBIApiAddRowsUrl) as System.Net.HttpWebRequest;
request.KeepAlive = true;
request.Method = "POST";
request.ContentLength = 0;
request.ContentType = "application/json";

//Add token to the request header
request.Headers.Add("Authorization", String.Format("Bearer {0}", token));

//JSON content for product row
string rowsJson = "{\"rows\":" +
"[{\"ProductID\":1,\"Name\":\"Adjustable Race\",\"Category\":\"Components\",\"ManufacturedOn\":\"07/30/2015\",\"NumberSold\":5}," +
"{\"ProductID\":2,\"Name\":\"LL Crankarm\",\"Category\":\"Components\",\"ManufacturedOn\":\"07/30/2014\",\"NumberSold\":5}," +
"{\"ProductID\":3,\"Name\":\"HL Mountain Frame - Silver\",\"Category\":\"Bikes\",\"ManufacturedOn\":\"07/30/2014\",\"NumberSold\":5}]}";

//POST web request
byte[] byteArray = System.Text.Encoding.UTF8.GetBytes(rowsJson);
request.ContentLength = byteArray.Length;

//Write JSON byte[] into a Stream
using (Stream writer = request.GetRequestStream())
{
writer.Write(byteArray, 0, byteArray.Length);

var response = (HttpWebResponse)request.GetResponse();

Console.WriteLine("Rows Added");

}
}

[Anonymous]

Thanks, @Jayendran - Someone asked 3 years ago - https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/9366600-update-rows-rest-api - still no movement, even though it was sent for review 3 years ago?

[Anonymous]

Steps to push data into a dataset

• Step 1: Register an app with Azure AD
• Step 2: Get an authentication access token
• Step 3: Create a dataset in Power BI
• Step 4: Get a dataset to add rows into a Power BI table
• Step 5: Add rows to a Power BI table

The next section is a general discussion of Power BI API operations that push data.

Power BI API operations to push data

With the Power BI REST API, you can push data sources to Power BI. When an app adds rows to a dataset, dashboard tiles update automatically with the new data. To push data, use the PostDataSet and PostRows operations. To find a dataset, use the get DataSet operation. You can pass a group ID to work with a group for any of these operations. To get a group ID list, use the get groups operation.

Here are the operations to push data into a dataset:

• PostDataset
• Get Datasets
• Post Rows
• Get Groups

You create a dataset in Power BI by passing a JavaScript Object Notation (JSON) string to the Power BI service.

The JSON string for a dataset has the following format:

Power BI Dataset JSON object

{"name": "dataset_name", "tables":
    [{"name": "", "columns":
        [{ "name": "column_name1", "dataType": "data_type"},
         { "name": "column_name2", "dataType": "data_type"},
         { ... }
        ]
      }
    ]
}

For our Sales Marketing dataset example, you would pass a JSON string as shown below. In this example, SalesMarketing is the dataset name, and Product is the table name. After defining the table, you define the table schema. For the SalesMarketing dataset, the table schema has these columns: ProductID, Manufacturer, Category, Segment, Product, and IsCompete.

Example dataset object JSON

{
    "name": "SalesMarketing",
    "tables": [
        {
            "name": "Product",
            "columns": [
            {
                "name": "ProductID",
                "dataType": "int"
            },
            {
                "name": "Manufacturer",
                "dataType": "string"
            },
            {
                "name": "Category",
                "dataType": "string"
            },
            {
                "name": "Segment",
                "dataType": "string"
            },
            {
                "name": "Product",
                "dataType": "string"
            },
            {
                "name": "IsCompete",
                "dataType": "bool"
            }
            ]
        }
    ]
}

I hope this information helps!

Regards,

Lewis

Developer

Apps4rent | O365CloudExperts

[Anonymous]

Thanks @Anonymous  - I did that, and each data insert, creates a new row. Ended up using a Gateway and refreshing for the historical data, and now looking at streaming for real-time. Thanks for the suggestion. 

Regards

[Jayendran · Accepted solution]

Hi @Anonymous ,

Unfortunately there are some less functionality available for Push DataSet API one of them is update the rows

You can find the available functionality for Push DataSet API below

https://docs.microsoft.com/en-us/rest/api/power-bi/pushdatasets

As per that only 2 functionality available for Rows ,either you can add new rows or delete rows 

Datasets DeleteRows

Datasets PostRows

I'd suggest you to provide this functionality as a new idea in  Idea Forum