conversion from dax to M (datetime group by hour)

[agathe_oui]

Hello,
I'm starting on Power BI and I can't do what I want to do...

I have several tables that I would like to merge together to make a summary graph. Here there representations:

table A

datetimeA		priceA
2020-05-22 15:00:00		18
2020-05-22 16:00:00		15

table B

datetimeB		priceB
2020-05-22 15:00:00		46
2020-05-22 16:00:00		45

table C

datetimeC		priceC
2020-05-22 15:00:00		12
2020-05-22 15:30:00		13
2020-05-22 16:00:00		15
2020-05-22 16:30:00		16

I need to average priceC values at hourly intervals as:

datetimeC_grouped		priceC_grouped
2020-05-22 15:00:00		12.5
2020-05-22 16:00:00		15.5

I found out how to average the priceC values at hourly intervals in DAX. I do it in my dashboard, by creating a new grouped_datetime column with a time step based on the datetime column in table C (at half_hourly step). Then I just have to ask for the average on the values of my graph. Here is my DAX code:

gouped_datetimeC = datevalue('tableC'[datetimeC] + TIME(hour('tableC'[datetimeC]), MINUTE('tableC'[datetimeC]) - MOD(MINUTE('tableC'[datetimeC]), 59), 0)

Now I'd like to merge tables A, B and C on the datetime column data. And here the problem comes...
Merge tables is done in Power Query, if I understand correctly it is more efficient.
Then I realize that I have to average my price at the hourly step of table C in Power Query...

I really don't know if this is possible and would like your help.
So is this the best method, or would it be better to merge the tables into DAX?

If my DAX code can be reviewed and better implemented, I would take your advice as well.

[dax · Accepted solution]

Hi @agathe_oui , 

You could refer to below code to transform Table C

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA1MNU1MlIwNLUyMAAiJR0lQyOlWB10SWOYpDGGpBlCpykWSbhOM6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [datetimeC = _t, priceC = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"datetimeC", type datetime}, {"priceC", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.ToText(DateTime.Date([datetimeC])) &" "& Number.ToText(Time.Hour([datetimeC]))),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"min", each List.Min([datetimeC]), type datetime}, {"avg", each List.Average([priceC]), type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Custom"})
in
    #"Removed Columns"

 Then click merge and choose corresponidng column to merge

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.

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.