Help, how to avoid wrong formatting of time data?

[Giuseppe_Aino]

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.
In certain conditions, it would seem when there is only one data item (and not always), Power BI represent a wrong time.

To understand better and for example, instead of representing "08:00" it randomly represents "30 Dec 08:00".

The context

Field [adetdate_short_10min] is part of an MS SQL view, [V_D_cdr_std], which is imported into the Power BI report and then poured into a table variable _SpecialDates (for custom filtering purposes).

The contents of the table variable as captured in Power BI is:

Briefly, the content of the field is:

[adetdate_short_10min] = "08:00"

I set field adetdate_short_10min to have the formatting "hh:nn" but the result is the following:

Why "30 Dec 08:00"? All this regardless of the actual content of the field and the imposed formatting.

Expected and generally respected behavior

The reason for this seemingly indeterminate behavior is unknown because in other circumstances I only have the time correctly:

Thanks in advance for your attention and time.

[v-hjannapu · Accepted solution]

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]

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!

[speedramps]

Try this ...

In Power Querychange the time column to a Time datatype

Then produce your report

I want to help you more but your description is too vague. Please write it again and provide example imput data (as a a table and not a screen print).

You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming

* Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want. (That is just crazy). ‌‌
* Please DO give a simple non-technical functional description of what you want
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Rename columns to user friendly names. Avoid your own system jargon that we may not understand.
* Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble. ‌‌
* Please click the thumbs up button for these helpful hints and tips. Thank you.

Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click” Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.

Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.

[Giuseppe_Aino]

Thank you for your help but changing the field type using Power Query doesn't solve the problem.

Thanks anyway because I didn't know it was possible to do it.

PS

Your attempt to reproduce my problem doesn't work simply because you put too much data on the x-axis, I only have one data point (if I had even one more it would have represented in the correct "hh:mm" format).