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 datet...
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.

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.