Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.