Forum Discussion
Post Patronspecific time from datetime field
jitpbi ,
In power query you can use Time.Hour
Time.Hour([Datetime])
if you want to remove data you can now filter between 10 to 16
if Time.Hour([Datetime]) >=10 and Time.Hour([Datetime]) <=16 then 1 else 0
Super Userjitpbi , Better you create a time column or use [Datetime].time in place ot time
time = [DateTime].time
Create formula for all measure you need
example
Predicted value M =calculate(Sum(Table[Predicted Value ]) , filter(Table, Table[time]>=time(10,0,0) && Table[Time]<=Time(16,0,0)))
Actual Value M =calculate(Sum(Table[Actual Value]) , filter(Table, Table[time]>=time(10,0,0) && Table[Time]<=Time(16,0,0)))
Or Create a time table. Join with table and use that as a filter. You can column of 24 Hours and use a range too
https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/
jitpbiHi amitchandak ,
Just thinking to do it in the edit query, if we can select from the datetime field for the time 10 AM to 4 PM. So the data will be loaded for 10 AM to 4 PM only. Can you please help me to do this in power query.
Thanks
- amitchandak
jitpbi ,
In power query you can use Time.Hour
Time.Hour([Datetime])
if you want to remove data you can now filter between 10 to 16
if Time.Hour([Datetime]) >=10 and Time.Hour([Datetime]) <=16 then 1 else 0
- jitpbi
Hi amitchandak ,
I am gettiing another challenege after republishing the report to service, where i replaced with the existing dataset, the "hour" column is not showing in the table. Also getting the data for entire time as before not for the time (>=10 &<= 16) i filtered in the power query. The same file in desktop was working fine where hour column is there in the table and data is filtered for the time >=10 &<= 16.
Can you please suggest what would be the reason and how to resolve this.
Thanks