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 need to calculate the differential production per Well and Year using power query, there is a special case when at the last year only have one date of production so I have to use the previous year last production, can you help me to do that? This What I mean:
Special case: 1200 (feb-21) - 1000 (april - 20 ) = 200
Year | Well | Production | Diferential Production Per Year Well A | |||
jan-19 | A | 100 | 2019 | 200 | ||
feb-19 | A | 200 | 2020 | 550 | ||
mar-19 | A | 250 | 2021 | 200 | ||
april-19 | A | 300 | ||||
jan-20 | A | 450 | Diferential Production Per Year Well B | |||
fen-20 | A | 600 | 2019 | 300 | ||
mar-20 | A | 7000 | 2020 | 300 | ||
april-20 | A | 1000 | Special case 2021 | 200 | ||
feb-21 | A | 1700 | ||||
mar-21 | A | 1900 | ||||
jan-19 | B | 300 | ||||
feb-19 | B | 400 | ||||
mar-19 | B | 500 | ||||
april-19 | B | 600 | ||||
jan-20 | B | 700 | ||||
fen-20 | B | 850 | ||||
mar-20 | B | 950 | ||||
april-20 | B | 1000 | ||||
feb-21 | B | 1200 |
Solved! Go to Solution.
Hi @OscarSuarez10,
I have used the same code as in your last question, just modified it a little bit. I've also used sample data from your last post.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY/BCsIwDEB/ZeS8QtMNxKPBD/DmoexQZ8VJ3aTM/7dUx8K2eCm0j+blWQtHN3oo4RSH67sdu6FPl7MPAZrSwsP1CpXRuE+vqHU6Dxnc/GUGhoGni+1dFaiKH6wYdK/YhflfzdBkMnoxcDJlUAmmDOtNU0a7rYH4bVrvwGpJqqV/tSTXklRLUu3axGqXJlZLYm0izQc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Production", Int64.Type}}), Year = Table.AddColumn(#"Changed Type1", "Year", each Date.Year([Date])), // till now data preparation // group by Well #"Grouped Rows by Well" = Table.Group(Year, {"Well"}, {{"AllRowsOfWell", each _, type table [Date=date, Production=number, Well=text, Year=number]}}), // group by year in every well TransformColumns = Table.AddColumn( #"Grouped Rows by Well", "Grouped Rows By Year", each let // group by Year and get min and max of production per year #"Grouped Rows" = Table.Group([AllRowsOfWell], {"Year"}, {{"MinOfYear", each List.Min([Production]), type number}, {"MaxOfYear", each List.Max([Production]), type number}}), // add an index columns #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1), // create column maxOfLastYear #"Added MaxOfLastYear" = Table.AddColumn(#"Added Index", "MaxOfLastYear", each try #"Added Index"{[Index] -1}[MaxOfYear] otherwise 0, type number), // calculate the difference #"Added Difference" = Table.AddColumn(#"Added MaxOfLastYear", "Difference", each if [MinOfYear] = [MaxOfYear] then [MaxOfYear] - [MaxOfLastYear] else [MaxOfYear] - [MinOfYear]) in #"Added Difference" ), #"Expanded Grouped Rows By Year" = Table.ExpandTableColumn(TransformColumns, "Grouped Rows By Year", {"Year", "Difference"}, {"Year", "Difference"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Grouped Rows By Year",{"AllRowsOfWell"}) in #"Removed Columns"
Hi @OscarSuarez10,
I have used the same code as in your last question, just modified it a little bit. I've also used sample data from your last post.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY/BCsIwDEB/ZeS8QtMNxKPBD/DmoexQZ8VJ3aTM/7dUx8K2eCm0j+blWQtHN3oo4RSH67sdu6FPl7MPAZrSwsP1CpXRuE+vqHU6Dxnc/GUGhoGni+1dFaiKH6wYdK/YhflfzdBkMnoxcDJlUAmmDOtNU0a7rYH4bVrvwGpJqqV/tSTXklRLUu3axGqXJlZLYm0izQc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Production", Int64.Type}}), Year = Table.AddColumn(#"Changed Type1", "Year", each Date.Year([Date])), // till now data preparation // group by Well #"Grouped Rows by Well" = Table.Group(Year, {"Well"}, {{"AllRowsOfWell", each _, type table [Date=date, Production=number, Well=text, Year=number]}}), // group by year in every well TransformColumns = Table.AddColumn( #"Grouped Rows by Well", "Grouped Rows By Year", each let // group by Year and get min and max of production per year #"Grouped Rows" = Table.Group([AllRowsOfWell], {"Year"}, {{"MinOfYear", each List.Min([Production]), type number}, {"MaxOfYear", each List.Max([Production]), type number}}), // add an index columns #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1), // create column maxOfLastYear #"Added MaxOfLastYear" = Table.AddColumn(#"Added Index", "MaxOfLastYear", each try #"Added Index"{[Index] -1}[MaxOfYear] otherwise 0, type number), // calculate the difference #"Added Difference" = Table.AddColumn(#"Added MaxOfLastYear", "Difference", each if [MinOfYear] = [MaxOfYear] then [MaxOfYear] - [MaxOfLastYear] else [MaxOfYear] - [MinOfYear]) in #"Added Difference" ), #"Expanded Grouped Rows By Year" = Table.ExpandTableColumn(TransformColumns, "Grouped Rows By Year", {"Year", "Difference"}, {"Year", "Difference"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Grouped Rows By Year",{"AllRowsOfWell"}) in #"Removed Columns"
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.