Adding calculated Rows using Power Query

[kar2022]

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.

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

Many Thanks.

VC

[AlexisOlson]

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.

[Anonymous]

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]

Hi Stephen,

Thanks.  Should I unpivot once the calculation is completed?

[Greg_Deckler]

@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.

[kar2022]

Hi Greg,

Thank you very much.  I solved the problem.  My only question is, I used "reference" to create a new sheet and added the formulas.  Is this the correct way to do it?. 

[kar2022 · Accepted solution]

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"

[kar2022]

If I add prior to the Append Query, what sytex should I use?

[kar2022]

Hi Greg,

I was to reference my file (prior to Append) and manage to do it.  Is this a good practice?.  Also, how do I add another custom formula?

(#"Added Custom1"),  In our example, I want to add a new "Cal3" (like we have Cal1 and Cal2).