How to label the date/time on X axis of a monthly max value?

[Anonymous]

Hi all,

I am attempting to build a bar chart in Power BI that mirrors an excel table detailing the max hourly value of every month. The excel version looks like this ("HE" means "hour ending". This is the time component):

What i have right now in Power BI is this:

How do i convert the X axis to Date & time for every month? For example: Jan 29, 6:00AM; Feb 28th, 5:00AM, etc?

I appreciate any help provided. Thank you.

[Anonymous]

My apologies, i did not explain my raw data table well enough. The excel file i showed above is what we are using now, but it is pulling from the same raw data as what i will be using in Power BI. I would like to move away from using that clunky excel file and substitute it with Power BI.

The chart you built is perfect, but i cannot recreate it. This is what the raw data table looks like in Power BI:

• I have the date/time column formatted as "date/time" in the query tool. That current format is exactly what i need for the X axis.
• I need the "Total rolling MW" column to show up how you did your table. Right now the "categorical" option under the X axis does not show up.

Here is how i have the data formatted in Visualizations currently:

Thank you for your help!

[dax · Accepted solution]

Hi helmsbk,

You could try to use below M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JRMjcBEkaWukCerqElkO3hqmCmFKsTreSWmgTkWpiC5C10gTy4vClY3jexKDkDKGBmBFJhpgvko6lwLCjKhBlhaKoL5MIVGBkpxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"2019" = _t, max = _t, date = _t, HE = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each [date]&" "&(Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([HE]),each if Value.Is(Value.FromText(_), type number) then _ else null)))&":00:00")),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type datetime}})
in
    #"Changed Type"

Then convert date format like below

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.