Forum Discussion

agathe_oui's avatar
agathe_oui
Frequent Visitor
6 years ago
Solved

conversion from dax to M (datetime group by hour)

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.

Need Help
  • dax's avatar
    dax
    6 years ago

    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.

2 Replies

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    6 years ago

    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.