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.
I need help, I am really new in the Power BI, this is my question:
I need to do this scenario:
1. Set up my current datetime to 7:00 am, for instance 20180614 7:00 am(Current time is 15:16)
2. Use the current date time 20180614 15:16 with the previous item to get the total minutes lapsed from 7:00 am to 15:16 pm
I really apreciate your help with this issue
🙂
Solved! Go to Solution.
Hello @Rigoleto,
At first glance I thought this was a simple problem however the issue for me was your datetime format beginning with '20180614 '.
The Query Editor should help you solve this:
let Source = /* I used an Excel file with a table named 'TableName' */ TableName_Table = Source{[Item="TableName",Kind="Table"]}[Data], /* Step 1 Replace the " " with 'T' to fulfill YYYYMMDDThh:mm:ss.nnnnnnn format requirement for DateTime.FromText ( ) */ #"Replaced Value" = Table.ReplaceValue(TableName_Table," ","T",Replacer.ReplaceText,{"StartDatetime"}), /* Step 2 use DateTime.FromText ( ) */ #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each DateTime.FromText([StartDatetime])), /* Step 3 get the current time */ #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each DateTime.LocalNow()), /* Step 4 if it didn't recognize the Type, change the type to datetime */ #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.1", type datetime}, {"Custom", type datetime}}), /* Step 5 get the duration in minutes */ #"Added Custom2" = Table.AddColumn(#"Changed Type", "Custom.2", each Duration.TotalMinutes([Custom.1]-[Custom])), /* Step 6 change the type */ #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Custom.2", type number}}) in #"Changed Type1"
Proud to be a Super User!
Hello @Rigoleto,
At first glance I thought this was a simple problem however the issue for me was your datetime format beginning with '20180614 '.
The Query Editor should help you solve this:
let Source = /* I used an Excel file with a table named 'TableName' */ TableName_Table = Source{[Item="TableName",Kind="Table"]}[Data], /* Step 1 Replace the " " with 'T' to fulfill YYYYMMDDThh:mm:ss.nnnnnnn format requirement for DateTime.FromText ( ) */ #"Replaced Value" = Table.ReplaceValue(TableName_Table," ","T",Replacer.ReplaceText,{"StartDatetime"}), /* Step 2 use DateTime.FromText ( ) */ #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each DateTime.FromText([StartDatetime])), /* Step 3 get the current time */ #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each DateTime.LocalNow()), /* Step 4 if it didn't recognize the Type, change the type to datetime */ #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.1", type datetime}, {"Custom", type datetime}}), /* Step 5 get the duration in minutes */ #"Added Custom2" = Table.AddColumn(#"Changed Type", "Custom.2", each Duration.TotalMinutes([Custom.1]-[Custom])), /* Step 6 change the type */ #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Custom.2", type number}}) in #"Changed Type1"
Proud to be a Super User!
Thanks!!!!!
I have used the logic and it is running as my expectation
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 |
---|---|
115 | |
100 | |
89 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |