Forum Discussion
Format Date_Time
Morning I have a date_time field and I want to only the date Field. This is the original data :
This is the table when I format and select short date , The issue is that Power Bi still considers the time and hence I have duplicate records of the same date, I want only 1 record for each of the dates.
I tried
You are seeing multiple dates because you simply changed the display or formatting but not the actual data type. Several methods to extract the date:
Hi Khomotjo ,
Thanks to danextian and uzuntasgokberk for their quick answers. I have some other thoughts to add:
(1) Here is my test data.(2) Create a calculation table. Select the Date column to change the data type.
Table = ADDCOLUMNS ( SUMMARIZE ( 'Table_3', [FinalisedDate], [id] ), "date", DATE ( YEAR ( [FinalisedDate] ), MONTH ( [FinalisedDate] ), DAY ( [FinalisedDate] ) ) )Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hello Khomotjo ,
Could you please try that navigate to Power Query(Transform Data), select Finalised_Date, Change Data Type to "Date/Time", Change Data Type "Date" and remove duplicates. This way is more performance and effective way.Here is the example:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE3MNc3MlMwNLMyNrcyMFOKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date Time" = _t]), #"Changed column type" = Table.TransformColumnTypes(Source, {{"Date Time", type datetime}}), #"Changed column type 1" = Table.TransformColumnTypes(#"Changed column type", {{"Date Time", type date}}), #"Removed duplicates" = Table.Distinct(#"Changed column type 1", {"Date Time"}) in #"Removed duplicates"Kind Regards,
Gökberk Uzuntaş
📌 If this post helps, then please consider Accepting it as a solution and giving Kudos — it helps other members find answers faster!
🔗 Stay Connected:
📘 Medium |
📺 YouTube |
💼 LinkedIn |
📷 Instagram |
🐦 X |
👽 Reddit |
🌐 Website |
🎵 TikTok |Thanks uzuntasgokberk I forgot to mention that this is a direct query in power BI. Also each line represent a unique order line and the exact time these orders were finalised. I am interested in the day and not necesarily when(time) the specific order line was finalised.
Hello Khomotjo ,
Okey, so you'd like to see only day values.Could you please try new calculated column(how to create calculated column: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-calculated-columns) to your table and write below the dax code:
" Day Column = DAY('YourTable'[YourDate])"And I'd like to mention that this error indicates that there is a circular dependency in the data model, first we can first check the relationship between the tables in the whole data model, if a table references itself directly or indirectly through other tables, a circular reference occurs.(https://community.fabric.microsoft.com/t5/Report-Server/COULDN-T-LOAD-THE-DATA-FOR-THIS-VISUAL-OLE-DB-or-ODBC-Error/m-p/3884227)
Kind Regards,
Gökberk Uzuntaş
📌 If this post helps, then please consider Accepting it as a solution and giving Kudos — it helps other members find answers faster!
🔗 Stay Connected:
📘 Medium |
📺 YouTube |
💼 LinkedIn |
📷 Instagram |
🐦 X |
👽 Reddit |
🌐 Website |
🎵 TikTok |
You are seeing multiple dates because you simply changed the display or formatting but not the actual data type. Several methods to extract the date:
Hi Khomotjo ,
Thanks to danextian and uzuntasgokberk for their quick answers. I have some other thoughts to add:
(1) Here is my test data.(2) Create a calculation table. Select the Date column to change the data type.
Table = ADDCOLUMNS ( SUMMARIZE ( 'Table_3', [FinalisedDate], [id] ), "date", DATE ( YEAR ( [FinalisedDate] ), MONTH ( [FinalisedDate] ), DAY ( [FinalisedDate] ) ) )Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
