Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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"
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.
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.
Hi Stephen,
Thanks. Should I unpivot once the calculation is completed?
@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.
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?.
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"
If I add prior to the Append Query, what sytex should I use?
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).