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.
Hola! tengo que hacer una suma de las precipitaciones anuales de varias estaciones meteorológicas, para lo cual tengo una columna de precipitaciones (en mm) una de la fecha de dicha precipitacion (día, mes, año) y otra del ID de la Estacion meteorológica. Cómo hago para poder obtener cuanto llovio por año por estación? Estuve googleando pero no encontré nada que entendiera como para poder hacerlo.
Desde ya muchisimas gracias por cualquier ayuda o guía.
Saludos!
Solved! Go to Solution.
Hi @ManucruzGR
You can put the following code in Advanced Editor in power query as a example:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc7RCcAgDATQXfwWkpxJtbNI91+jaqk10p9w8LhwtYaSTUMMoJPAwi2qtJNLuOLURFIcG1YGwSt7HWXIPwsTvKt3kDpO9rDa8Q0vPcp4t+o7fDLYl+E0bbqV+7DrBg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [d_Estacion = _t, Fecha = _t, id_Campaña = _t, #"Precipitacion (mm)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"d_Estacion", Int64.Type}, {"Fecha", type date}, {"id_Campaña", Int64.Type}, {"Precipitacion (mm)", Int64.Type}}),
#"Extracted Year" = Table.TransformColumns(#"Changed Type",{{"Fecha", Date.Year, Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Extracted Year", {"d_Estacion", "Fecha"}, {{"Sum", each List.Sum([#"Precipitacion (mm)"]), type nullable number}})
in
#"Grouped Rows"
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @ManucruzGR
You can put the following code in Advanced Editor in power query as a example:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc7RCcAgDATQXfwWkpxJtbNI91+jaqk10p9w8LhwtYaSTUMMoJPAwi2qtJNLuOLURFIcG1YGwSt7HWXIPwsTvKt3kDpO9rDa8Q0vPcp4t+o7fDLYl+E0bbqV+7DrBg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [d_Estacion = _t, Fecha = _t, id_Campaña = _t, #"Precipitacion (mm)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"d_Estacion", Int64.Type}, {"Fecha", type date}, {"id_Campaña", Int64.Type}, {"Precipitacion (mm)", Int64.Type}}),
#"Extracted Year" = Table.TransformColumns(#"Changed Type",{{"Fecha", Date.Year, Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Extracted Year", {"d_Estacion", "Fecha"}, {{"Sum", each List.Sum([#"Precipitacion (mm)"]), type nullable number}})
in
#"Grouped Rows"
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, this is te table (a portion of it):
id_Estacion | Fecha | id_Campaña | Precipitacion (mm) |
87540 | 2/9/2010 | 41 | 78 |
87540 | 18/3/2010 | 41 | 52 |
87540 | 2/2/2010 | 41 | 50 |
87540 | 18/2/2010 | 41 | 50 |
87540 | 28/10/2010 | 41 | 40 |
87540 | 12/4/2010 | 41 | 35 |
and this is the output format:
Id_Estacion | Año | Precipitaciones (mm) |
8754 | 2010 | 588 |
8754 | 2011 | 893 |
8456 | 2018 | 1236 |
Update the source step in the code below.
Does this work?
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc3BCQAxCETRXjwHHI0SU0uw/zaSva1ehIHH9xyK5QYahM1QVsg3TN5ZQTl+YLJEAa4VaC84OuiFCgSsFVgrGEt9MZ0yLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id_Estacion = _t, Fecha = _t, id_Campaña = _t, #"Precipitacion (mm)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"id_Estacion", Int64.Type}, {"Fecha", type date}, {"id_Campaña", Int64.Type}, {"Precipitacion (mm)", Int64.Type}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Fecha]), Int64.Type),
#"Grouped Rows" = Table.Group(#"Inserted Year", {"id_Estacion", "Year"}, {{"Rainfall (mm)", each List.Sum([#"Precipitacion (mm)"]), type nullable number}})
in
#"Grouped Rows"
@ManucruzGR if this resolves the query please accept it as the solution so others can access it faster. Thanks !
can you post sample data / columns / pbix file for reference after removing sensitive data and also a sample output?
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.
User | Count |
---|---|
49 | |
27 | |
20 | |
15 | |
12 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |