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 a...
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"

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.