topic Re: Updating Data with Push Push Data Sets in Developer

Updating Data with Push Push Data Sets

Anonymous — Mon, 13 Jan 2020 22:57:33 GMT

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");

}
}

Re: Updating Data with Push Push Data Sets

Jayendran — Thu, 16 Jan 2020 05:17:46 GMT

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

Re: Updating Data with Push Push Data Sets

Anonymous — Thu, 16 Jan 2020 15:37:30 GMT

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

Re: Updating Data with Push Push Data Sets

Anonymous — Fri, 17 Jan 2020 09:07:47 GMT

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

Re: Updating Data with Push Push Data Sets

Anonymous — Fri, 17 Jan 2020 09:15:11 GMT

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?