cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: conversion from dax to M (datetime group by hour)

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.

View solution in original post

2 REPLIES 2
Highlighted
Super User IV
Super User IV

Re: conversion from dax to M (datetime group by hour)

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.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Community Support
Community Support

Re: conversion from dax to M (datetime group by hour)

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.

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Kudoed Authors