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

Difrential production Special case

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

 

YearWellProductionDiferential Production Per Year Well A
jan-19A100 2019200 
feb-19A200 2020550 
mar-19A250 2021200 
april-19A300    
jan-20A450Diferential Production Per Year Well B
fen-20A600 2019300 
mar-20A7000 2020300 
april-20A1000 Special case 2021200 
feb-21A1700    
mar-21A1900    
jan-19B300    
feb-19B400    
mar-19B500    
april-19B600    
jan-20B700    
fen-20B850    
mar-20B950    
april-20B1000    
feb-21B1200    
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Difrential production Special case

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"

Capture.PNG

View solution in original post

3 REPLIES 3

Re: Difrential production Special case

Hello @Nolock  can you help me with is one? Thank You

Super User
Super User

Re: Difrential production Special case

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"

Capture.PNG

View solution in original post

Re: Difrential production Special case

Thank You @Nolock  it worked Perfectly!

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (2,185)