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

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

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

Highlighted

Re: Difrential production Special case

Thank You @Nolock  it worked Perfectly!

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 6 members 2,821 guests
Please welcome our newest community members: