Forum Discussion

kar2022's avatar
kar2022
Frequent Visitor
3 years ago
Solved

Adding calculated Rows using Power Query

Hi,

I am struggling to add rows in the same table using Power Query.  Basically, I want to used the row data (by date) and create additional rows (Cal1 and Cal2) for the same dates and calculating as below.

 

DatePlayerScore
6/1/2022Player1-0.1197
6/1/2022Player2-0.1250
6/1/2022Player3-0.1159
5/1/2022Player1-0.0159
5/1/2022Player2-0.0166
5/1/2022Player3-0.0066
4/1/2022Player1-0.1349
4/1/2022Player2-0.1169
4/1/2022Player3-0.1098
6/1/2022Cal1(Player1, 6/1 score)*0.25 + (Player2, 6/1 score)*0.4 + (Player3, 6/1 score)*0.35]
6/1/2022Cal2(Player1, 6/1 score)*0.56  + (Player3, 6/1 score)*0.44]
5/1/2022Cal1(Player1, 5/1 score)*0.25 + (Player2, 5/1 score)*0.4 + (Player3, 5/1 score)*0.35]
5/1/2022Cal2(Player1, 5/1 score)*0.56  + (Player3, 5/1 score)*0.44]
4/1/2022Cal1(Player1, 4/1 score)*0.25 + (Player2, 4/1 score)*0.4 + (Player3, 4/1 score)*0.35]
4/1/2022Cal2(Player1, 4/1 score)*0.56  + (Player3, 4/1 score)*0.44]

 

Many Thanks.

VC

 

 

Need Help
  • kar2022's avatar
    kar2022
    3 years ago

    Hi Greg,

     

    Thank you very much.  It works fine.  However, when I make changed in my file it does not works.  I "Appended" a new data file with "Date", "Player", "Score" for a new player.

     

    Where would I add it?.  Not very good with "M".  Thanks for the helpl

     

        Custom = Table.Combine(#"Added Custom"[Custom],{"Player”,"Date", "Score","PrevScore"}),

        #"Added Custom1" = Table.AddColumn(Custom, "Player", each ([Score]-[PrevScore])/[PrevScore]),

        #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Score", "PrevScore"}),

        #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Date", Player", "Score"}),

        #"Appended Query" = Table.Combine({#"Reordered Columns", Captain})

    in

        #"Appended Query"

8 Replies

Replies have been turned off for this discussion
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi kar2022 ,

     

    You could try to pivot columns and then get the calculated results.

    Pivot columns - Power Query | Microsoft Docs

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • kar2022's avatar
      kar2022
      Frequent Visitor
      3 years ago

      Hi Stephen,

      Thanks.  Should I unpivot once the calculation is completed?

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        3 years ago

        kar2022 Correct, take a look at this:

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc8xDoAwCIXhuzCrBdqi3MK96eDg5uTm7TWmbLAxfOHlbw0kUWJkhgn263jOm75rxoVIV+iTA3gAruiDbB+q/qAGExgCNiDigzGBOECJKnJRH1gFSQCsAnWD3l8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Player = _t, Score = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Player", type text}, {"Score", type number}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Player]), "Player", "Score", List.Sum),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Cal1", each [Player1] * .25 + [Player2] * .4 + [Player3] * .35),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each [Player1] * .56 + [Player3] * .44),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Custom1", {"Date"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

        Just paste into a Blank query using the Advanced Editor.

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    3 years ago

    Here's a method that should generalize well if you have lots more players and calculations:

    let
    Weights = #table(
        type table [Player = Text.Type, Calc1 = Number.Type, Calc2 = Number.Type],
        {{"Player1", 0.25 , 0.56},{"Player2", 0.4, null},{"Player3", 0.35, 0.44}}
    ),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc8xDoAwCIXhuzCrBdqi3MK96eDg5uTm7TWmbLAxfOHlbw0kUWJkhgn263jOm75rxoVIV+iTA3gAruiDbB+q/qAGExgCNiDigzGBOECJKnJRH1gFSQCsAnWD3l8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Player = _t, Score = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Player", type text}, {"Score", type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Player"}, Weights, {"Player"}, "Weights", JoinKind.LeftOuter),
    #"Expanded Weights" = Table.ExpandTableColumn(#"Merged Queries", "Weights", {"Calc1", "Calc2"}, {"Calc1", "Calc2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Weights",{"Player"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Date", "Score"}, "Player", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Product", each [Score] * [Value], type number),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Date", "Player"}, {{"Score", each List.Sum([Product]), type number}}),
    #"Appended Query" = Table.Combine({#"Changed Type", #"Grouped Rows"})
in
    #"Appended Query"

    This merges in the weights corresponding to each player and calculation, unpivots the calculations, takes the product of the score and weight value, uses Group By to sum the products, and appends these calculated rows to the end of the starting table.

     

    In practice, your Weights would be specified in a different table like the following but I've included the definition in the query above so that it's entirely self-contained and you can just paste it as-is into the Advanced Editor of a new blank query.