Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi Guys,
I have the dataset one excell file with schedule update
I have collumn start and time for my filters . But i need one function, i need to count all the rows if they have time range 20:00:00 hours to 22:00:00 in all days excepted satuday, because i need to know too if the day is saturday count all range hour 08:00 to 22:00 hours. It's possible?
My collumn on dataset
Solved! Go to Solution.
Thanks, that's much easier to work with.
See attached PBIX file.
Let me know if you have any questions.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Click on the 'Transform Data' button and see the individual steps, or see the code below.
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"ZdBLCsAgDATQq5SsBfPRRnMV8f7XaIW0oq4fMzDTGlRECEAaUSIj80XGAj0MoVXUUF14l5RcZJViTC5pF2GXPKT8wmj0he6TspOeVJzKRjQL66A616K9B/T+AA==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [ID = _t, DATA = _t]
),
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"ID", Int64.Type}, {"DATA", type datetime}}
),
#"Inserted Day Name" = Table.AddColumn(
#"Changed Type",
"Day Name",
each Date.DayOfWeekName([DATA]),
type text
),
#"Inserted Time" = Table.AddColumn(
#"Inserted Day Name",
"Time",
each DateTime.Time([DATA]),
type time
),
#"Added Custom" = Table.AddColumn(
#"Inserted Time",
"Custom",
each
if [Day Name] = "Saturday" and [Time] >= #time(8, 0, 0) and [Time] <= #time(22, 0, 0) then
1
else if [Day Name] <> "Saturday" and [Time] >= #time(20, 0, 0) and [Time] <= #time(22, 0, 0) then
1
else
0
)
in
#"Added Custom"
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Yes it's possible.
Can you please paste some actual data (not a screenshot)?
Also, a screenshot of your data model would be useful?
Are you using a date dimension?
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Hi Dear Friend,
Thank you for your fast asnwer,
My data in excell is:
Data: format as date and time
ID | DATA |
900 | 17/03/2022 01:23 |
901 | 17/03/2022 07:07 |
902 | 17/03/2022 07:44 |
903 | 17/03/2022 08:21 |
904 | 17/03/2022 08:32 |
905 | 18/03/2022 20:11 |
906 | 18/03/2022 20:15 |
907 | 18/03/2022 20:18 |
908 | 18/03/2022 21:11 |
This helps?
Thanks, that's much easier to work with.
See attached PBIX file.
Let me know if you have any questions.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Hi KNP
how do i see as formulas? or you can explain me how you did?
Best regards,
Click on the 'Transform Data' button and see the individual steps, or see the code below.
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"ZdBLCsAgDATQq5SsBfPRRnMV8f7XaIW0oq4fMzDTGlRECEAaUSIj80XGAj0MoVXUUF14l5RcZJViTC5pF2GXPKT8wmj0he6TspOeVJzKRjQL66A616K9B/T+AA==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [ID = _t, DATA = _t]
),
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"ID", Int64.Type}, {"DATA", type datetime}}
),
#"Inserted Day Name" = Table.AddColumn(
#"Changed Type",
"Day Name",
each Date.DayOfWeekName([DATA]),
type text
),
#"Inserted Time" = Table.AddColumn(
#"Inserted Day Name",
"Time",
each DateTime.Time([DATA]),
type time
),
#"Added Custom" = Table.AddColumn(
#"Inserted Time",
"Custom",
each
if [Day Name] = "Saturday" and [Time] >= #time(8, 0, 0) and [Time] <= #time(22, 0, 0) then
1
else if [Day Name] <> "Saturday" and [Time] >= #time(20, 0, 0) and [Time] <= #time(22, 0, 0) then
1
else
0
)
in
#"Added Custom"
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Amazing, thank you for your help Sir.
Best regards!
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
109 | |
100 | |
39 | |
31 |