Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have created a date-table in DAX based on following function: Date = CALENDAR(DATE(2012;1;1) ; (DATE(2020;12;31)))
I need to create a DATETIME column with all hours from 00.00 - 23.00 per date, i.e. 24 rows per date. The format should be "dd.mm.yy hh:mm:ss". Is there any formula to create such a column? If not, is there any method to create such a table in power query?
Thank you!
Solved! Go to Solution.
Hi
You can create it like this in Power Query
let Source = List.Dates(#date(2018,3,25), 1, #duration(1,0,0,0)), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each {0..23}), #"Expanded {0}" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Added Custom1" = Table.AddColumn(#"Expanded {0}", "DateTime", each DateTime.From([Date])+#duration(0,[Custom],0,0)) in #"Added Custom1"
Modify the first step to create a longer period.
BR
/Erik
Hi @Anonymous,
Based on my test, we can take the following steps to meet your requirement.
1. Enter a time table from 0:00:00-23:00:00.
2.Create the table using the formula as below.
DateTime = ADDCOLUMNS ( CROSSJOIN ( CALENDAR ( DATE ( 2012, 1, 1 ), DATE ( 2020, 12, 31 ) ), 'Time' ), "DateTime", [Date] + [Time] )
3. Then we can get the result as we need.
For more details, please check the pbix as attached.
https://www.dropbox.com/s/8z0il9k8vy8l588/time.pbix?dl=0
Regards,
Frank
Hi @Anonymous,
Based on my test, we can take the following steps to meet your requirement.
1. Enter a time table from 0:00:00-23:00:00.
2.Create the table using the formula as below.
DateTime = ADDCOLUMNS ( CROSSJOIN ( CALENDAR ( DATE ( 2012, 1, 1 ), DATE ( 2020, 12, 31 ) ), 'Time' ), "DateTime", [Date] + [Time] )
3. Then we can get the result as we need.
For more details, please check the pbix as attached.
https://www.dropbox.com/s/8z0il9k8vy8l588/time.pbix?dl=0
Regards,
Frank
Hi
You can create it like this in Power Query
let Source = List.Dates(#date(2018,3,25), 1, #duration(1,0,0,0)), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each {0..23}), #"Expanded {0}" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Added Custom1" = Table.AddColumn(#"Expanded {0}", "DateTime", each DateTime.From([Date])+#duration(0,[Custom],0,0)) in #"Added Custom1"
Modify the first step to create a longer period.
BR
/Erik
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |