Forum Discussion

Giuseppe_Aino's avatar
Giuseppe_Aino
Advocate II
1 year ago
Solved

Help, how to avoid wrong formatting of time data?

Thanks in advance to anyone who wants to help.   The problem I have a field, [adetdate_short_10min], of type varchar(5) (in a DB MS SQL) intended to contain ONLY TIME data in the "hh:mm" format. ...
Need Help
  • v-hjannapu's avatar
    v-hjannapu
    1 year ago

    Hi Giuseppe_Aino,

    Thank you  for reaching out to the Microsoft fabric community forum.

    The reason you are seeing something like "30 Dec 08:00" instead of just "08:00" is because Power BI sometimes auto-converts time values especially text format like "08:00" into full datetime format when used on chart axes. When this happens, it adds a default date like 30 Dec, even if your original data has only time.


    To avoid this:Change the X-axis to “Categorical” in the visual settings. This prevents Power BI from converting text to datetime. Make sure [adetdate_short_10min] is set as "Text" type in the model view not datetime
    .Alternatively, if you change the column type to “Time” in Power Query, Power BI will treat it as proper time and display only the time without adding any date. This also works well if you plan to use it on time-based visuals.

    If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated.
    Best Regards,
    Harshitha.

v-hjannapu's avatar
v-hjannapu
Community Support
1 year ago

Hi Giuseppe_Aino,

Thank you  for reaching out to the Microsoft fabric community forum.

The reason you are seeing something like "30 Dec 08:00" instead of just "08:00" is because Power BI sometimes auto-converts time values especially text format like "08:00" into full datetime format when used on chart axes. When this happens, it adds a default date like 30 Dec, even if your original data has only time.


To avoid this:Change the X-axis to “Categorical” in the visual settings. This prevents Power BI from converting text to datetime. Make sure [adetdate_short_10min] is set as "Text" type in the model view not datetime
.Alternatively, if you change the column type to “Time” in Power Query, Power BI will treat it as proper time and display only the time without adding any date. This also works well if you plan to use it on time-based visuals.

If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated.
Best Regards,
Harshitha.

Giuseppe_Aino's avatar
Giuseppe_Aino
Advocate II
1 year ago
 
Thank you Harsh1tha for your help, your analysis is correct.
 
Changing from "Continuos" to "Categorical" represented the value as expected in the "hh:mm" format, this also with the side effect of dilating the length of the x-axis.
This is the solution that I will propose to the end customer.
 
I cannot use the text format also because that same field is used effectively in other pages and would distort them.
 
I tried to change the format of the field from text to time by acting from Power Query but unfortunately it did not have the desired effect. 
 
And so:
Thanks, honestly I did not know there was also this level of action.
 
Thank you!