Push dynamic date table using Power BI API

[Anonymous]

For a project we are pushing data via the Power BI API using the following operations:

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

We would like to use time logic (YTD, Sameperiodlastyear) in our report and we therefor need a 'date table'. See: https://docs.microsoft.com/en-us/power-bi/desktop-date-tables

The solution we build does not allow us to create a dataset using Power BI desktop so we need to create the dataset using the API. 

Is there a way to create a dynamic date table in a 'Push dataset' (a dataset created by the API)? The best way would be to create a calculated table with a DAX statement such as CALENDARAUTO().

As a last resort we could push a date table with actual dates in the rows but if possible I want to use a calculated table. 

[Anonymous · Accepted solution]

Hi,

Yep this is a royale pain in the neck. The Push DataSet does not appear to be able to apply a "IsDateTable" attribute to a dataset.

I also tried to use Measures to create the colums, using CALENDARAUTO() howver, this does not appear to work either.

As a hack solution for this, I generate a table using the API, then simply generate the columns in code: ex: In fact, even more is broken, as you cannot "sort" a visual by a non-visual column, as is often required for Month, or other Alpha fields, as the default sort is Alph ion the visual. Sure would like for this to work a bit better.

Since you cant designate a table as a "Special Date Table", you have to explicitly add a relationship to the Date and cols.

Simple hack code:

var dataRowsReq = new PostRowsRequest();

// PSH - Because I cant get CALENDARAUTO() DAX to work, gonna generate them here...
int monthIdx = 0;
int quarterIdx = 0;
var calPrev = new CalendarPbi();
var dateStart = new DateTime(2015, 1, 1);
var dateEnd = new DateTime(2021, 12, 31);
var dates = new List<CalendarPbi>();
for (var dt = dateStart; dt <= dateEnd; dt = dt.AddDays(1))
{
var cal = new CalendarPbi();
cal.Date = dt.ToString("MM/dd/yyyy");
cal.Year = String.Format ("CY {0:yyyy}", dt);
cal.Month = String.Format("{0:yyyy MM}", dt); // PBI BrainDead - Need to sort here...
cal.Quarter = String.Format("{0:yyyy} Q{1}", dt, ((dt.Month + 2) / 3));

// Sort Idxss...
if (cal.Month != calPrev.Month)
{
monthIdx++;
}

if (cal.Quarter != calPrev.Quarter)
{
quarterIdx++;
}
cal.MonthIdx = monthIdx;
cal.QuarterIdx = quarterIdx;

dates.Add(cal);
calPrev = cal;
}

// Paginate load into PowerBI...
var inc = 10000;
var skip = 0;
var cnt = 0;
while (cnt < dates.Count())
{
dataRowsReq.Rows = dates.Skip(skip).Take(inc).Cast<Object>().ToList();
client.Datasets.PostRowsInGroup(workspaceId, datasetId, TABLE_NAME_CALENDAR, dataRowsReq);

cnt += inc;
skip += inc;
}

[Anonymous]

Thank you for your reply @Anonymous ! I've accepted your answer as the solution although we know this is not the functionality that we need. 

[v-shex-msft]

HI @Anonymous,
I don't think it is possible to use Dax functions in power bi rest API. (perhaps you can use the programming language to generate a date table and convert its format and push to the dataset. Notice: this date table need to manually update)
In my opinion, I'd like to suggest you try to use power bi desktop to create a report with a dynamic calendar table and publish this to power bi service.  After these steps, you can use rest api to create new data tables and push data to that dataset.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft , thanks for the reply. What do you mean by "perhaps you can use the programming language to generate a date table and convert its format and push to the dataset"?

"I'd like to suggest you try to use power bi desktop to create a report with a dynamic calendar table and publish this to power bi service.  After these steps, you can use rest api to create new data tables and push data to that dataset."

I thought about this but I don't think this is possible. We are creating datasets programmatically based on users that login to an application. The report is cloned based on a report template. We are therefor not able to manually create the date table in Power BI Desktop. 

[v-shex-msft]

HI @Anonymous,

>>What do you mean by "perhaps you can use the programming language to generate a date table and convert its format and push to the dataset"?

It means you can create a table based on looping functions(for, while) to generate a calendar table from the start date to end date, then convert this table to correspond push dataset table formula.

>>I thought about this but I don't think this is possible.

In fact, I double-check on push dataset api and find it only allow you to use on push dataset. For a dataset that publishes from the report, it seems not allow you to works with push dataset rest API.
Regards,
Xiaoxin Sheng