How to change predefined date format to a custom one in Tabular Editor

[Sergiy]

Hi,

I plan to change a format string of all the columns in my data model using Tabular Editor for that purpose. The code will be something like the following:

foreach(var column in Model.Tables.SelectMany(t => t.Columns)){
    if(column.DataType.ToString() == "DateTime") {
        column.FormatString = "d/m/yyyy";
    }
}

The problem is I achieve what I aim to if only I've changed "Date time format" column property in Power BI desktop beforehand from the specified "common date format" to "Custom".

If any of "common date formats" is specified as a date time format for my Date column then running my code does not bring any visible effect.

I guess I should not only change FormatString property, but somewhere specify "Custom". Problem is I don't know where.

It seems that Tabular Editor UI doesn't provide that possibility either.

Any ideas are welcome.

By the way, I can't find any property that can be used to discern system tables like "LocalDateTable_f6715c49-833c-40bd-baed-84e9a81c9d16" from the ones I created myself. Is there a way?

[amitchandak]

@Sergiy , Not very clear. There is an option under model view. Not sure you will get that - https://docs.microsoft.com/en-us/power-bi/desktop-custom-format-strings

[Sergiy]

@amitchandak ,

>There is an option under model view

I will quote the docs you referred to:

>Once you've selected Custom from the Format drop down, you can select from a list of commonly used format strings.

Once I've selected Custom from the Format drop down, my code will work. That is what I was trying to convey.

My purpose is to change format string programmatically not via Power BI desktop UI.

The line:

column.FormatString = "d/m/yyyy";

will only make changes to a column format if Custom is selected from the Format drop down in Power BI desktop for the target column.

Have a look:

On a picture "A" a predefined date format is specified as a column format. In this case "column.FormatString = "d/m/yyyy"" will not be enough to force a column to change its format. 

On a picture "B" I chose "Custom" for date time format. I performed it in Power BI desktop. Once I selected Custom from the Date time format drop down, my code would work and I could specify a format string I need.

So, is there a way in "Date time format" drop down to change General Date(or any of common date formats) to Custom using Tabular Editor script for that purpose? What should be changed/added in my code?

[v-shex-msft]

HI @Sergiy,

Current you can't configure up the custom mode as the default format of date fields.

AFAIK, the default data format is based on your system region format, you can change your system 'region' format settings then this format will apply to the default DateTime format of power bi desktop.

Region and Language Settings 

Regards,

Xiaoxin Sheng

[Sergiy]

I see that I am not understood.

>Current you can't configure up the custom mode as the default format of date fields.

It is not my intention to have a custom format as a delault for date fields. No.

What I need is to use Tabular Editor and for a date field that has "*3/14/2001 1:30:55 PM (General Date)" as a predefined format change that format to be a custom one, for example "d/m/yyyy".

I'd like to be able to use Tabular Editor for that purpose. Tabular Editor is a tool that is designed to serve such a purpose. Isn't it? And it works... but to make it work I am forced to switch back to Power BI desktop and chose "Custom" from "Date time format" drop down. Once it is done I can proceed with Tabular Editor setting any custom format I'd like.

So the point is how to make this change not in Power BI but in Tabular Editor. What property should be initialized? What's its name? I guess my code should look somewhat like this:

column.UknownPropertyName = "Custom";
column.FormatString = "d/m/yyyy";

I will be very grateful for sharing ideas of how to achieve my goal.

[v-shex-msft]

Hi @Sergiy,

So you mean you want to find out a method to change the default style in the tabular editor instead of power bi, right? If this is a case, I think this should more be related to the tabular editor, you can try to post this to tabular related forums to get further support. 

BTW, the 'general date' format is dynamic based on your system language and region settings. If you want to use a specific format, it should be better to choose a specific format instead of the 'general date'. (it is being defined internal and you can't change its format)

In addition, you can also try to change tabular editor properties settings if it works.

Regards,

Xiaoxin Sheng

[Sergiy]

Hi @v-shex-msft 

>So you mean you want to find out a method to change the default style in the tabular editor instead of power bi, right?

Yes, I do. I'm searching for a way to change format string of some of the date type columns in the data model. There are too many columns that require changes of a format string. That's why I am using Tabular Editor. This tool allows to perform the task easily and fast. The pbix file I am working on is distributed among the customers that have there own preferences regarding the date format. To satisfy there needs I wrote a script in Tabular Editor. As I stated before I haven't succeeded yet.

>you can also try to change tabular editor properties settings if it works

Unfortunately, changes made via Tabular Editors UI directly are not reflected on a model as well.

>you can try to post this to tabular related forums to get further support. 

I posted a question on GitHub to Daniel Otykier:

https://github.com/otykier/TabularEditor/issues/684

Daniel advised me to try some steps. I tried, but the problem still remains (today is November 22).

[Sergiy]

Daniel Otykier conciders the discribed behaviour as a Power BI bug:

https://github.com/otykier/TabularEditor/issues/684#issuecomment-732697268

@v-shex-msft , if it's in your power to escalate the issue that would be great if you did so.

[v-shex-msft]

HI @Sergiy,

Sure, you can refer to the following steps to create a support ticket with your report to the power bi team to get further support.

submit a support ticket

Regards,

Xiaoxin Sheng