PushDataset Table source

[Anonymous]

Hi, 

I am trying to make a push dataset with table that have source from other table created in the dataset. Say, we have a dataset, where is table Product with columns as ID, Name and Description. I want make a other table RefProduct which will be connected to table Product. I can make it in Power BI Desktop, where it easy to make, but I can´t find right syntax doing it for push dataset. If it able to do, is there a option make a filter too?

Thanks, Martin  

PS: I want make that push dataset by API.

[Anonymous]

Hi @Anonymous ,

You can refer the following link to create the dataset in the workspace:

Push Datasets - Datasets PostDatasetInGroup

{
   "name":"dataset name",
   "defaultMode":"Push",
   "tables":[
      {
         "name":"Product",
         "columns":[
            {
               "name":"ID",
               "dataType":"Int64"
            },
            {
               "name":"Name",
               "dataType":"string"
            },
            {
               "name":"Description",
               "dataType":"string"
            }
         ]
      }
   ]
}

And you can refer the following blog to create relationships among tables in a dataset through Power BI REST API. 

Relationship

“relationships”: [

{

“name”: “2ea345ce-b147-436e-8ac2-9d3c4d82af8d”,

“fromTable”: “sales”,

“fromColumn”: “Date”,

“toTable”: “Date”,

“toColumn”: “Date”,

“crossFilteringBehavior”: “bothDirections”

},

{

“name”: “5d95f419-e589-4345-9581-6e70670b1bba”,

“fromTable”: “forecast”,

“fromColumn”: “date”,

“toTable”: “Date”,

“toColumn”: “Date”,

“crossFilteringBehavior”: “bothDirections”

}

]

Best Regards

[Anonymous]

Oh, thanks for response, but this isn´t solution what I expected. 

Look, in documentation I found what properties table has.

There is a property source and it should be array of ASMashupExpression, so I tried create one table in push dataset like this.

{
  "name": "SalesDataset",
  "tables":[
    {
      "name": "Product",
      "columns": [
        {
          "name": "ProductID",
          "dataType": "Int64"
        },
        {
          "name": "Name",
          "dataType": "string"
        },
        {
          "name": "Category",
          "dataType": "string"
        }
      ]
    },
    {
      "name": "Reference",
      "columns": [
        {
          "name": "Category",
          "dataType": "string"
        },
        {
          "name": "Count",
          "dataType": "Int64"
        }
      ],
      "source":[
          "SUMMARIZECOLUMNS(Product[Category], \"Count\", Count(Product[ProductID])"
    }
  ]
}

So it created a dataset with two tables, but table Reference will have specific DAX expression and create table from table Product. But when I use it in API, it throws an error. "Cannot deserialize"

{
      "name": "Reference",
      "columns": [
        {
          "name": "Category",
          "dataType": "string"
        },
        {
          "name": "Count",
          "dataType": "Int64"
        }
      ],
      "source":[
          "SUMMARIZECOLUMNS(Product[Category], \"Count\", Count(Product[ProductID])"
      ]
  
}

So, I tried to use function PutTable, but there it throws error too, but there is more specific. 

"The property with name '' was found with a value node of type 'PrimitiveValue'; however, a complex value of type 'Microsoft.PowerBI.ServiceContracts.ASModel.Metadata.ASMashupExpression' was expected."