Display Customers in Time Arrival Slots

[matthew.wright]

Hello,

I am trying to write a measure that will allow me to count how many customers purchased items at what time of the day. For example how many customers bought something between 9:00 AM and 10:00 AM.

I have a column in my table that has just times purchased:

I am also trying to understand trends on different days. So I have a date calendar I have linked to the table as well.

I thought about using the switch function but I am not having much luck with it.

Could anyone help?

[Melissa]

Hi @matthew.wright,

Do you have a Time Dimension table in your Model?
If not here’s a topic on that. BTW you will probably want to cut the Time down to the Minute or Hour level if that is the granularity of reporting. Because that will have a huge impact on the cardinality of the column, model size and performance.

Time Table (Power Query M function)

Hi everyone, @DavidNealon thought it would be a nice addition if there was a Time Table Power Query M code function available in this category as well. Now I can’t take any credit because it was designed by Gina Meronek, you can find it and the full article she wrote here. When invoked the function creates a Time Table at a granularity of Minutes. If your requirement is different that can easily be fixed. Note that other changes might be required as well but to give you some ideas: For Hourl…

[matthew.wright]

Melissa,

Thanks for sending this. I added a time table in addition to the date table I have. I tried to do a lookup value from the time a customer purchased to the period of the day to do counts but it gave me blanks.

I also still can’t seem to link specific hours either.

See below: Any thoughts?

test = LOOKUPVALUE(‘Invoked Function’[Period of Day],‘Invoked Function’[Time],Patient[Time_Created])

Returning blanks for me?

[Melissa]

Yeah the granularity of the Time table is at the Minute level and your fact data at the Second so these don’t match… Like I suggested earlier you will probably want to cut the Time down to the Minute or Hour level if that is the granularity of reporting.That will have a huge impact on the cardinality and thus model size and performance.

[matthew.wright]

Melissa,

I see now. Do you know if there is an easy way from the Query Editor to update my time to remove the seconds?

[Melissa]

Hi @matthew.wright

Paste this into a new blank query, shows 2 options one for DateTime and one for Time without seconds.

let
    Source = DateTime.FixedLocalNow(),
    ConvertToTable = #table(1, {{Source}}),
    DateTime_NoSeconds = Table.AddColumn(ConvertToTable, "Custom", each DateTime.From ( DateTime.ToText( [Column1], "yyyy-MM-dd HH:mm") ), type datetime),
    TimeOnly = Table.AddColumn(DateTime_NoSeconds, "Time", each DateTime.Time([Column1]), type time),
    Time_NoSeconds = Table.AddColumn(TimeOnly, "Custom.1", each Time.From ( Time.ToText( [Time], "HH:mm") ), type time)
in
    Time_NoSeconds 

I hope this is helpful.

[matthew.wright]

Melissa,

I appreciate all your help. I’m not sure how this query helps me. I want to convert the column in an existing table to remove the seconds from the time.

When I run this query it only gives me a single date and time:

Could you elaborate further? I am still a little new at this?

[Melissa]

My apologies…

You have shown a “Time_Created” column that is of type time I would expect…
Go to that Query and on the “Add Column” tab select “Custom Column” a dialog box will be shown:

There you can enter this M code:
Time.From ( Time.ToText( [Time_Created], “HH:mm”) )

Finally in the formula bar (before the closing parenthesis) you can declare type, as shown here:

[matthew.wright]

@Melissa

let
    Source = DateTime.FixedLocalNow(),
    ConvertToTable = #table(1, {{Source}}),
    DateTime_NoSeconds = Table.AddColumn(ConvertToTable, "Custom", each DateTime.From ( DateTime.ToText( [Column1], "yyyy-MM-dd HH:mm") ), type datetime),
    TimeOnly = Table.AddColumn(DateTime_NoSeconds, "Time", each DateTime.Time([Column1]), type time),
    Time_NoSeconds = Table.AddColumn(TimeOnly, "Custom.1", each Time.From ( Time.ToText( [Time], "HH:mm") ), type time)
in
    Time_NoSeconds 

That did the trick. Thank you so much!